CODEWITHSUNDEEP
sql-serversql-server-2008datetime
haki
haki

Reputation: 9759

SQL Server 2008 inconsistent results when converting datetime to varchar

I have a table with two datetime columns and I'm trying to convert them to an iso string format using the following query:

select 
   CONVERT(VARCHAR(23), date1, 126) as date1,   
   CONVERT(VARCHAR(23), date2, 126) as date2
from   
   some_table

But I'm getting two different results, one with milliseconds and one without

date1                        date2
2015-03-11T05:16:04.663      2015-03-11T05:15:43

I've looked at the create table script and they are both defined as datetime. I have no clue how the data is being inserted.

How can I get both columns to return with milliseconds ?

Upvotes: 0

Views: 80

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

SQL Server "helpfully" will trim the milliseconds portion if it's entirely 0. If you need the 0 milliseconds included (I can't imagine what you're doing where you need .000 to be included) then you'll have to detect the trimming and re-add them:

;With Converted as (
    --Your existing query. For this example, I'm just using one date:
    select CONVERT(varchar(23),CONVERT(datetime,'2015-03-01T05:15:43.000'),126) as date2
)
select
    CASE
        WHEN LEN(date2) = 19 THEN date2 + '.000'
        ELSE date2
    END as date2
from Converted

(And, again if for some bizarre reason you really need the end result to be a varchar(23) rather than a varchar(27) you'll have to add another CONVERT that wraps the CASE expression because the system's not smart enough to realise that any value that the CASE returns could always fit in a varchar(23))

Upvotes: 1

CiucaS
CiucaS

Reputation: 2128

It is becouse the second date has 0 ms. 

CREATE TABLE #Test ( date1 datetime, date2 datetime)

INSERT INTO #Test VALUES ('2015-03-11 05:16:04.663','2015-03-11 05:15:43' )

INSERT INTO #Test VALUES ('2015-03-11 05:16:04','2015-03-11 05:15:43.55' )


select 
   CONVERT(VARCHAR(23), date1, 126) as date1,   
   CONVERT(VARCHAR(23), date2, 126) as date2
from   
   #Test

Check this example.

Upvotes: 0

Related Questions