Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 384
  • 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
 
TimCotteeHead of Software ServicesCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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