asked on

Converting Date/Time into something more useful

Experts,

I have a NVARCHAR field in my table called CreatedDate. Here is a sample of the data that is in this field: 2008-05-15T16:42:50. I need to be able to convert this data into: 05-15-2008 16:42:50. I have tried a few combinations of CONVERT statements, but nothing seems to be working. All help is appreciated.

BTW

I am running SQL Server 2000

TimCottee

Hello MarkRod,

You just need to change the "T" to " " e.g.,

Declare @Something varchar(50)
Set @Something = '2008-05-15T16:42:50'
Select cast(replace(@Something,'T',' ') as datetime)

Regards,

TimCottee

Guy Hengel [angelIII / a3]

>I have a NVARCHAR field in my table called CreatedDate.
why is that not datetime data type, actually?
I guess because it's loaded from external data source, seeing the data?

anyhow, here we go:

CONVERT(VARCHAR(11), CONVERT(datetime, REPLACE(yourfield, 'T', ' '), 105) + CONVERT(VARCHAR(8), CONVERT(datetime, REPLACE(yourfield, 'T', ' '), 8)

Open in new window

garethh86

Hope the link below helps!

https://www.experts-exchange.com/questions/23332283/Select-Statement-to-convert-a-varchar-to-datetime-w-nulls.html

MarkRod

ASKER

Tim,

I did that but I still was not able to then convert the date format from yyyy-mm-dd

MarkRod

ASKER

I use this in my select statement: CONVERT(NVARCHAR(40), REPLACE([CreatedDate],'T',' '), 101). My output is 2008-05-15 16:42:50. Shouldn't it be 05/15/2008 16:42:20?

Guy Hengel [angelIII / a3]

>Shouldn't it be 05/15/2008 16:42:20?
no, because the convert, when getting a varchar input, will not assume datetime...

didn't you try my suggestion?

MarkRod

ASKER

I can't get it to work right

Guy Hengel [angelIII / a3]

Indeed, I omitted a part in the expression...

declare @t varchar(30)
set @t = '2008-05-15T16:42:50'
select @t original
  , CONVERT(CHAR(11), CONVERT(datetime, REPLACE(@t, 'T', ' '), 120) , 105) 
  + CONVERT(VARCHAR(8), CONVERT(datetime, REPLACE(@t, 'T', ' '), 120), 8) 
  result

Open in new window

MarkRod

ASKER

Almost there, with what you just posted it puts the date in dd-mm-yyyy format. I need it in mm-dd-yyyy format. I appreciate your help.

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MarkRod

ASKER

That does it! Thanks for everything!

Guy Hengel [angelIII / a3]

this time, it's me to thank you for closing the question,
as this one pushed me over the 10M barrier (savant level) in the MS SQL Server zone!
CHeers