Date format question

Question

Date format question

gsd1

Default port

Points: 1442
More actions
August 2, 2012 at 7:45 am

#279808

Hi,
For "07/13/2012 1:06:12 AM" --> I am getting "2012-07-13T01:06:12.000-04:00"
How can I just take date&time without considering "-04:00" in tsql?
Thank you..

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

gsd1 (8/2/2012)
Hi,
For "07/13/2012 1:06:12 AM" --> I am getting "2012-07-13T01:06:12.000-04:00"
How can I just take date&time without considering "-04:00" in tsql?
Thank you..

Display of a SQL Server datetime column is dependant on your local settings and the client you are using.

We're a little in the dark here - what are you displaying, and using what?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

gsd1 Default port Points: 1442 More actions · Answer 2

Thanks for the reply Chris.

For "07/13/2012 1:06:12 AM" --> Web service is returning "2012-07-13T01:06:12.000-04:00" .

I defined my table column as datetime and the program is loading it as "2012-07-13 05:06:12.000"

I want that to be loaded as "2012-07-13 01:06:12.000"

Am I clear now?

Regards,

gsd1 Default port Points: 1442 More actions · Answer 3

I tried like this...

SELECT convert(datetime, '2012-08-01T15:05:13.000', 126) --> this is working fine

SELECT convert(datetime, '2012-08-01T15:05:13.000-04:00', 126) --> but this is not

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

gsd1 (8/2/2012)
Thanks for the reply Chris.
For "07/13/2012 1:06:12 AM" --> Web service is returning "2012-07-13T01:06:12.000-04:00" .
I defined my table column as datetime and the program is loading it as "2012-07-13 05:06:12.000"
I want that to be loaded as "2012-07-13 01:06:12.000"
Am I clear now?
Regards,

SQL Server stores datetime values in a proprietary format, what you are seeing is a representation of that data formatted by the client you are using.

gsd1 (8/2/2012)
I tried like this...
SELECT convert(datetime, '2012-08-01T15:05:13.000', 126) --> this is working fine
SELECT convert(datetime, '2012-08-01T15:05:13.000-04:00', 126) --> but this is not

I think you have answered your own question here - it's not storage which is the problem, it's display. The two statements above convert from string to datetime, try the other way around using GETDATE() for the second parameter.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ant-Green SSC Guru Points: 113507 More actions · Answer 5

The problem is with the offset, you would first need to convert the string to a offset then to a datetime

Like this

SELECT convert(datetime,convert(datetimeoffset, '2012-08-01T15:05:13.000-04:00'))

This will then add on the 4 hours to the time as the time is 4 hours behind server time.

So you will get a result of

2012-08-01 19:05:13.000

winston Smith SSCoach Points: 19500 More actions · Answer 6

why not use the international standard ISO8601. Its compatable with all date formats.

http://msdn.microsoft.com/en-us/library/ms187819.aspx

http://www.w3.org/TR/NOTE-datetime

gsd1 Default port Points: 1442 More actions · Answer 7

My requirement is as the following

My xml file from web service:

I am pulling data into the temp table like the following:

SELECT XMLTable.lastUpdateDate lastUpdateDate

into #tempdata

FROM OPENXML(@hDoc, 'Rows/row', 2)

WITH ( lastUpdateDate datetime2) XMLTable

When i query #tempdata lastUpdateDate is showing as below

2009-10-07 19:19:23.0000000

But I need it like this (UTC time should not be considered)

2009-10-07 15:19:23.0000000

gsd1 Default port Points: 1442 More actions · Answer 8

Here is the script that I am trying to use to get lastUpdateDate....as "2009-10-07T15:19:23.000" from the xml file

DECLARE @hDoc int

exec sp_xml_preparedocument @hDoc OUTPUT,'<Rows><row><lastUpdateDate>2009-10-07T15:19:23.000-04:00</lastUpdateDate></row></Rows>'

SELECT XMLTable.lastUpdateDate lastUpdateDate

FROM OPENXML(@hDoc, 'Rows/row', 2)

WITH ( lastUpdateDate datetime2) XMLTable

Thank you....

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

gsd1 (8/2/2012)
My requirement is as the following
My xml file from web service:
<Rows><row><lastUpdateDate>2009-10-07T15:19:23.000-04:00</lastUpdateDate></row></Rows>
I am pulling data into the temp table like the following:
SELECT XMLTable.lastUpdateDate lastUpdateDate
into #tempdata
FROM OPENXML(@hDoc, 'Rows/row', 2)
WITH ( lastUpdateDate datetime2) XMLTable
When i query #tempdata lastUpdateDate is showing as below
2009-10-07 19:19:23.0000000
But I need it like this (UTC time should not be considered)
2009-10-07 15:19:23.0000000

You do realize that you are changing the data if you store 2009-10-07T15:19:23.000-04:00 as 2009-10-07 15:19:23.0000000 because they are not the same date/time values. You are using SQL Server 2008, is there any reason (other than the column is defined datetime2) that you can't store the date/time using the UTC offset?