Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 378
  • Last Modified:

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
0
MarkRod
Asked:
MarkRod
1 Solution
 
TimCotteeCommented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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

0
 
garethh86Commented:
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
MarkRodAuthor Commented:
Tim,

I did that but I still was not able to then convert the date format from yyyy-mm-dd
0
 
MarkRodAuthor Commented:
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?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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?
0
 
MarkRodAuthor Commented:
I can't get it to work right
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
 
MarkRodAuthor Commented:
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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
change the 105 to 110:
http://msdn.microsoft.com/en-us/library/ms187928.aspx
declare @t varchar(30)
set @t = '2008-05-15T16:42:50'
select @t original
  , CONVERT(CHAR(11), CONVERT(datetime, REPLACE(@t, 'T', ' '), 120) , 110) 
  + CONVERT(VARCHAR(8), CONVERT(datetime, REPLACE(@t, 'T', ' '), 120), 8) 
  result

Open in new window

0
 
MarkRodAuthor Commented:
That does it! Thanks for everything!
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now