Convert varchar to datetime without millisecond

Question

Write simple this query:

select cast('2020-10-10 07:30:00.0000000' as datetime)

and want to show

'2020-10-10 07:30:00'

but SQL Server return this error:

Conversion failed when converting date and/or time from character string.

How can solve that problem? Thanks.

According to the documentation learn.microsoft.com/en-us/sql/t-sql/data-types/…, For datetime, the valid time ranges 00:00:00 through 23:59:59.997 . What you want as 07:30:00 is just presentation issue. Do the formatting at your front end application — Squirrel
– Squirrel, Commented Dec 16, 2020 at 5:57

Dale K · Accepted Answer · 2020-12-16 05:58:02Z

A datetime doesn't have that level of precision, instead use datetime2:

select cast('2020-10-10 07:30:00.0000000' as datetime2);

And to only show the desired string, cast it to a shorter string:

select cast(cast('2020-10-10 07:30:00.0000000' as datetime2) as varchar(19));

Obviously it makes no sense to cast a string to date and back again, but I assume you have simplified your actual use-case. Otherwise you could use:

select cast('2020-10-10 07:30:00.0000000' as varchar(19));

1 Answer 1