converting string to date

[Mark]

Hello,

I try to convert a pseudo datetime string into a date. In Oracle I can do
to_date( MyDate, 'yyyymmddhh24miss' ); how I can do this with MS SQL ?

thanks and regards
Mark

[Simon Hayes]

See CONVERT() in Books Online - it supports a number of different
formats, although not the exact one you've mentioned above. You might
need to look at modifying the string before using CONVERT() - see
"String Functions" in Books Online, or you could also consider doing
that in your client application.

Simon

[Hugo Kornelis]

Hi Mark,

The easiest way is to use string functions to convert your date to the
unambiguous ISO-standard yyyy-mm-ddThh:mm:ss format, then cast to
datetime:

DECLARE @DateString char(14)
SET @DateString = '20050812204332'
SELECT CAST(SUBSTRING(@DateString, 1, 4) + '-'
+ SUBSTRING(@DateString, 5, 2) + '-'
+ SUBSTRING(@DateString, 7, 2) + 'T'
+ SUBSTRING(@DateString, 9, 2) + ':'
+ SUBSTRING(@DateString, 11, 2) + ':'
+ SUBSTRING(@DateString, 13, 2) AS datetime)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)