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, 'yyyymmddhh24mi ss' ); how I can do this with MS SQL ?

thanks and regards
Mark

[Simon Hayes]

Re: converting string to date

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]

Re: converting string to date

On Fri, 12 Aug 2005 14:17:57 +0200, Mark wrote:
[color=blue]
>Hello,
>
>I try to convert a pseudo datetime string into a date. In Oracle I can do
>to_date( MyDate, 'yyyymmddhh24mi ss' ); how I can do this with MS SQL ?
>
>thanks and regards
>Mark
>[/color]

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(@Date String, 5, 2) + '-'
+ SUBSTRING(@Date String, 7, 2) + 'T'
+ SUBSTRING(@Date String, 9, 2) + ':'
+ SUBSTRING(@Date String, 11, 2) + ':'
+ SUBSTRING(@Date String, 13, 2) AS datetime)

Best, Hugo
--

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