Search code examples
sqlsql-servert-sql

Convert varchar to datetime without millisecond

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.

Solution

  • 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));