SQL Server Performance Forum – Threads Archive

How to read YYYY-MM-DDThh:mm:yy+(-)hh:mm in SQL

How can i read/convert the following date format: – [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]I tried following but it doesn’t work print isdate(‘2004-11-22T08:11:12+22:11’) and suggestions reasons prointers ill be helpful.

You’d have to write your own function 2004-11-22T08:11:12 is a valid date, so split the date at the fifth to last character (+ or – sign) and then add the 22 hours and 11 minutes to the resulting date Cheers
Twan

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by nitingulati7 How can i read/convert the following date format: – [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]I tried following but it doesn’t work print isdate(‘2004-11-22T08:11:12+22:11’) and suggestions reasons prointers ill be helpful. <hr height="1" noshade id="quote"></blockquote id="quote"> SELECT SUBSTRING(‘2004-11-22T08:11:12+22:11′,1,10) +’ ‘ +SUBSTRING(‘2004-11-22T08:11:12+22:11′,12,<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> …will return as ‘2004-11-22 08:11:12′ SELECT ISDATE(SUBSTRING(‘2004-11-22T08:11:12+22:11′,1,10) +’ ‘ +SUBSTRING(‘2004-11-22T08:11:12+22:11′,12,<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />) …will return a value 1 (True) If you are trying to add or subtract 22 hours and 11 minutes to or from that date then it gets a bit tricky: select dateadd(minute,(60 * convert(smallint, SUBSTRING(‘2004-11-22T08:11:12+22:11’,20,1) +SUBSTRING(‘2004-11-22T08:11:12+22:11’,21,2))) +convert(smallint,SUBSTRING(‘2004-11-22T08:11:12+22:11’,20,1) +SUBSTRING(‘2004-11-22T08:11:12+22:11’,24,2)), convert(smalldatetime,SUBSTRING(‘2004-11-22T08:11:12+22:11′,1,10) +’ ‘ +SUBSTRING(‘2004-11-22T08:11:12+22:11′,12,<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />)) …will return as ‘2004-11-23 06:22:00′ which calculates from ‘2004-11-22 08:11:12’ with 22 hours and 11 minutes added. You will notice I constantly use the original ‘2004-11-22T08:11:12+22:11’ value in all the functions so you could write a user-defined function or stored procedure from this where you only need to pass the original string and the function should return a valid date value from it.

If the first part is a valid datetime then +/1 doesnt really matter (since if you add/subtract hours and minutes from a valid date, its still a valid date. So it can be quite simple select isdate(replace(left(Passedfield,len(Passedfield)-6),’T’,’ ‘))

Hi Chris, To read the first part you don’t even need to replace the T as that is a valid ISO standard that SQL recognises Cheers
Twan

]]>