SQL Cast Datetime

How to cast the DateTime to Time

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.

datetime Cast or Convert?

convert has an optional parameter style, and I suggest to use convert instead of cast. It helps to avoid confusion.
For example, if you write cast('20130302' as date), what would you get? March 2 or February 3?

Also, if you want specific format when casting to date to string, you bound to use convert

Casting 0 as DATE and DATETIME

I think it's a matter of what Microsoft chose to support. More or less it comes down to the fact that data conversions to datetime are allowed from numeric values, while date does not allow conversions from numeric values. Within SQL server, it must be converting the 0 first to a numeric value like 0.000000000 and then to the datetime equivalent of that number. My example shows that it's possible to convert the current date into a numeric value and then back to a datetime. However, converting to a date throws an error. I would guess to avoid rounding issues that you may have if you tried to convert the value 0.5 to a date. Would program the SQL engine to implicitly convert 0.5 to the date 1900-01-01 or 1900-01-02 in that case. You'd have to make arbitrary decisions on what date should be returned in that case.

This works:

--produces a numeric value like 42746.97660799
select cast(getdate() as numeric(18,8)) 

select cast(42746.97660799 as datetime)

While this throws the same error you received:

select cast(42746.97660799 as date)

Msg 529, Level 16, State 2, Line 5
Explicit conversion from data type numeric to date is not allowed.

How to convert DateTime to VarChar

With Microsoft Sql Server:

--
-- Create test case
--
DECLARE @myDateTime DATETIME
SET @myDateTime = '2008-05-03'

--
-- Convert string
--
SELECT LEFT(CONVERT(VARCHAR, @myDateTime, 120), 10)

Converting a Date to Datetime gives error in sql

Look at the values that cannot be converted:

select invday
from tableA
where try_cast(invday as date) is null and invday is not null;

It is also unclear if your format is mm/dd/yyyy or dd/mm/yyyy. You can specify a format using convert():

-- mm/dd/yyyy
select invday
from tableA
where try_convert(date, invday, 101) is null and
      invday is not null;

-- dd/mm/yyyy
select invday
from tableA
where try_convert(date, invday, 103) is null and
      invday is not null;

How to get Time from DateTime format in SQL?

SQL Server 2008:

SELECT cast(AttDate as time) [time]
FROM yourtable

Earlier versions:

SELECT convert(char(5), AttDate, 108) [time]
FROM yourtable

How to convert text column to datetime in SQL

Use convert with style 101.

select convert(datetime, Remarks, 101)

If your column is really text you need to convert to varchar before converting to datetime

select convert(datetime, convert(varchar(30), Remarks), 101)

---

Related Topics