DateTime Format Conversion Issue

Hello everyone,

I came accross this problem where I can't change my stored date to:

m/d/yyyy hh:mi:ss AM

Seems to me like there is no code related to that particular conversion.

I'm just assuming that because I've tried all the codes supplied with the convert, and the closest that I got was

selectCONVERT(nvarchar(max),getdate(),22)

02/27/07 12:24:40 PM

m/d/yy hh:mi:ss AM

So can any one give me a quick solution for this? I'd appreciate it

select

RIGHT(CAST(100+DATEPART(MM,createdDate)ASCHAR(3)),2)+'/'

+RIGHT(CAST(100+DATEPART(DD,createdDate)ASCHAR(3)),2)+'/'

+CAST(DATEPART(YYYY,createdDate)ASCHAR(4))+' '

+CASEWHENDATEPART(HH,createdDate)< 13

THENRIGHT(CAST(100+DATEPART(HH,createdDate)ASCHAR(3)),2)

ELSECAST(DATEPART(HH,createdDate)-12ASCHAR(2))

END+':'

+DATENAME(ss, createdDate)+':'

+RIGHT(CAST(100+DATEPART(MI,createdDate)ASCHAR(3)),2)

+CASEWHENDATEPART(HH,createdDate)< 13

THEN' AM'

ELSE' PM'

END

from files

|||

How about

selectCONVERT(nvarchar(max),getdate(),101)+substring(CONVERT(nvarchar(max),getdate(),22), 9, 50)

?

Dan