CODEWITHSUNDEEP
sqlsql-server-2008
Pink
Pink

Reputation: 77

Convert varchar to datetime in sql which is having millisec

I have a column abc varchar(100) with data like 2011-09-26 16:36:57.810000

I want to convert this column to DATETIME...

But doing a 

Convert(DATETIME, abc,120)

is giving this error:

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

Can any one please help me convert my varchar format to datetime in SQL Server 2008?

Thanks in advance

Upvotes: 5

Views: 17511

Answers (5)

Arsen Khachaturyan
Arsen Khachaturyan

Reputation: 8330

Assuming we have the following string variables:

DECLARE @d VARCHAR(100)  = '2020-04-06T04:35:07.9490051Z' -- 7 digits nanoseconds
DECLARE @d1 VARCHAR(100) = '2020-04-05T15:00:00Z'         -- simple: without nanoseconds

I came up to the solution using CAST operator:

SELECT CAST(LEFT(@d,19) + 'Z' AS DATETIME)  -- outputs: 2020-04-06 04:35:07.000
SELECT CAST(LEFT(@d1,19) + 'Z' AS DATETIME) -- outputs: 2020-04-05 15:00:00.000

Upvotes: 0

kamilk
kamilk

Reputation: 4039

Based on GBrian's answer, I came up with:

CONVERT(DATETIME, CONVERT(DATETIME2, abc, 126))

I haven't benchmarked how this stacks up against the substring-based solutions.

Upvotes: 0

GBrian
GBrian

Reputation: 1061

In case you need 6 digits precision use DATETIME2

SELECT CONVERT(DATETIME2, '2016-08-09T08:08:50.358000', 126) as MSSQLDateTime2
SELECT CONVERT(DATETIME, '2016-08-09T08:08:50.358', 126) as MSSQLDateTime

Upvotes: 6

Kaf
Kaf

Reputation: 33809

You can use style 121 but you can have only 3 digits for milliseconds (i.e yyyy-mm-dd hh:mi:ss.mmm(24h)) format.

declare @abc varchar(100)='2011-09-26 16:36:57.810' 
select convert(datetime,@abc,121)

So you can sort it out by limiting the varchar field to 23 characters before converting as:

declare @abc varchar(100)='2011-09-26 16:36:57.810000' 
select convert(datetime,convert(varchar(23),@abc),121)

Or use the Left() function to get first 23 characters as:

select convert(datetime,left(@abc,23),121)

Try to avoid storing date as string.

Upvotes: 9

ifx
ifx

Reputation: 581

SQL Server only supports 3 decimal places for milliseconds, so the following will work:

Convert(DATETIME, SUBSTRING(abc, 0, 24) ,120)

Upvotes: 0

Related Questions