How to cast the DateTime to Time

by

Time is not stored with its display format in SQL Server.
Therefore, from the user perspective, you can say that it has no format.
Of course, that’s not completely accurate since it does have a storage format, but as an average user you can’t really use it.
This is true for all date and time data types:
Date, DateTimeOffset, DateTime2, SmallDateTime, DateTime and Time.

If you need a format then you don’t need to cast to time but to a char. Use Convert to get the char you need:

SELECT CONVERT(char(10), [time], 108) as CSTTime

Here is some background data if you’re interested:

In this article published in 2000 the writer explains in depth how SQL Server treats dates and times. I doubt if anything significant changed between 2000 and 2015 in the way SQL Server stores date, time and datetime values internally.

Here are the relevant quotes, if you don’t want to read all of it:

So how does SQL Server internally store the dates? It uses 8 bytes to store a datetime value—the first 4 for the date and the second 4 for the time. SQL Server can interpret both sets of 4 bytes as integers.
……..
……..
SQL Server stores the second integer for the time as the number of clock ticks after midnight. A second contains 300 ticks, so a tick equals 3.3 milliseconds (ms).

since time is actually stored as a 4 byte integer, it really doesn’t have a format as an integral part of the data type.

You might also want to check out this article for a more detailed explanation with code samples.

More Related Contents:

  1. Is it possible to write single query on two table which are not connected to each other?
  2. Can I Comma Delimit Multiple Rows Into One Column? [duplicate]
  3. How can I schedule a job to run a SQL query daily?
  4. Reset identity seed after deleting records in SQL Server
  5. How to avoid the “divide by zero” error in SQL?
  6. Multiple INSERT statements vs. single INSERT with multiple VALUES
  7. Split function equivalent in T-SQL?
  8. Querying data by joining two tables in two database on different servers
  9. Is having an ‘OR’ in an INNER JOIN condition a bad idea?
  10. Real life example, when to use OUTER / CROSS APPLY in SQL
  11. Where value in column containing comma delimited values
  12. How to find all connected subgraphs of an undirected graph
  13. How to use table variable in a dynamic sql statement?
  14. SQL query to split column data into rows
  15. Bulk DELETE on SQL Server 2008 (Is there anything like Bulk Copy (bcp) for delete data?)
  16. SQL Server 2008: how do I grant privileges to a username?
  17. Create Trigger to log SQL that affected table?
  18. Transfer data from one database to another database
  19. How to find third or nᵗʰ maximum salary from salary table?
  20. SQL “between” not inclusive
  21. Can I set ignore_dup_key on for a primary key?
  22. Selecting first row per group
  23. Using PIVOT in SQL Server 2008
  24. How do I prevent a database trigger from recursing?
  25. sql group by only rows which are in sequence
  26. Function in SQL Server 2008 similar to GREATEST in mysql?
  27. Converting Select results into Insert script – SQL Server
  28. How to retrieve field names from temporary table (SQL Server 2008)
  29. When using GETDATE() in many places, is it better to use a variable?
  30. Cumulative Total in MS Sql server [duplicate]

Leave a Comment