CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2005sql-server-2008-r2
Vikas Kunte
Vikas Kunte

Reputation: 731

SQL - Converting String to Date and Time

I need to convert a value to a SQL Server DateTime.

Data looks like below :

I tried 

CONVERT(DATE, '20161021-12:55:16.000', 102)

and it throws an error.

Can I get help in SQL to convert these samples into a valid date time?

Also these values are in UTC.

I need to convert them to EST.

Appreciate your help.

Upvotes: 0

Views: 281

Answers (4)

John Cappelletti
John Cappelletti

Reputation: 81930

No need for the CONVERT 102. 

Select cast(replace('20161021-12:55:16.000','-',' ') as datetime)

Returns

2016-10-21 12:55:16.000

To Convert From UTC to Local Server. One should keep in mind that the time difference will change with Daylight Savings Time (EDT/EST)

Select DateAdd(MINUTE,DateDiff(MINUTE,GetUTCDate(),GetDate()),cast(replace('20161021-12:55:16.000','-',' ') as datetime))

Reuturns

2016-10-21 07:55:16.000

Upvotes: 4

Rich Benner
Rich Benner

Reputation: 8113

If you're confident with your format being consistent then you can just take that dash out (I've used datetime for illustration purposes);

CONVERT(DATETIME, REPLACE('20161021-12:55:16.000','-',' '), 102)

Result

2016-10-21 12:55:16.000

For the conversion you can convert to datetime, take off your hours and then convert to the date format you want;

CONVERT(DATETIME, DATEADD(hh,-5,CONVERT(DATETIME,REPLACE('20161021-12:55:16.000','-',' '))), 102)

Result;

2016-10-21 07:55:16.000

To get a better idea of this, set it before 5am and you will get the previous day;

CONVERT(DATETIME, DATEADD(hh,-5,CONVERT(DATETIME,REPLACE('20161021-04:55:16.000','-',' '))), 102)

Result

2016-10-20 23:55:16.000

For reference, on my test systems you don't need to convert to 102, that's the default.

DATEADD(hh,-5,CONVERT(DATETIME,REPLACE('20161021-04:55:16.000','-',' ')))

To get the time difference dynamically do something like this;

DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),GETDATE()),CONVERT(DATETIME,REPLACE('20161021-04:55:16.000','-',' ')))

Upvotes: 6

Sankar
Sankar

Reputation: 7107

Try this...

select CONVERT(DATETIME, replace('20161021-12:55:16.000','-',' '), 102)

For EST

select dateadd(HH,-5,CONVERT(DATETIME, replace('20161021-12:55:16.000','-',' '), 102))

Upvotes: 0

Mansoor
Mansoor

Reputation: 4192

 SELECT CONVERT(datetime,SWITCHOFFSET(CONVERT(datetimeoffset, 
                                REPLACE('20161021-12:55:16.000','-',' ')), 
                        DATENAME(TzOffset, SYSDATETIMEOFFSET()))) 
   AS ColumnInLocalTime

Upvotes: 0

Related Questions