Date Formatting

[Ed75]

One more question here folks..

I have a varchar(10) field that is storing a date as YYYY-MM-DD and I need the output to be MM-DD-YYYY is there an easy way to accomplish this? Thanks

 

[ptheriault]

Here is a list of popular date formats

PRINT '1) HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>' + 
CONVERT(CHAR(19),GETDATE())  
PRINT '2) HERE IS MM-DD-YY FORMAT ==>' + 
CONVERT(CHAR(8),GETDATE(),10)  
PRINT '3) HERE IS MM-DD-YYYY FORMAT ==>' + 
CONVERT(CHAR(10),GETDATE(),110) 
PRINT '4) HERE IS DD MON YYYY FORMAT ==>' + 
CONVERT(CHAR(11),GETDATE(),106)
PRINT '5) HERE IS DD MON YY FORMAT ==>' + 
CONVERT(CHAR(9),GETDATE(),6) 
PRINT '6) HERE IS DD MON YYYY HH:MM:SS:MMM(24H) FORMAT ==>' + 
CONVERT(CHAR(24),GETDATE(),113)

you want to use this one.

CONVERT(CHAR(10),GETDATE(),110)

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[SQLBill]

Paul,

The OP probably can't use that....they are 'storing' the dates as VARCHAR not as DATETIME.

Ed75,

You could convert it to DATETIME and back to VARCHAR. Or you could 'pull out' each part into variables and then put it back in the order you want. Best yet would be to store dates as they are supposed to be stored - DATETIME values.

-SQLBill

Posting advice: FAQ481-4875

 

[Ed75]

I'd love to convert this and store tham the way they are supposed to be stored, but then the third party application that uses this database would bomb on me.

I might just have to the the peices apart somehow.

 

[monksnake]

This will do it:

convert(char(10), dateStringField, 110)

<.

 

[monksnake]

I have to convert string stored dates ALLLLLL the time, OHHHH sorry Paul didn't really pay attention, you already got it.

<.

 

[SQLBill]

Check out CHARINDEX, PATINDEX, and SUBSTRING for options. If the value is always the same (yyyy-mm-dd), then SUBSTRING will be good enough.

DECLARE @year VARCHAR(4)
DECLARE @month VARCHAR(2)
DECLARE @day VARCHAR(2)
SET @year = (SELECT SUBSTRING(mydate,1,4))
SET @month = (SELECT SUBSTRING(mydate, 6,2))
SET @day = (SELECT SUBSTRING(mydate,9,2))
SELECT @month + '-' + @day + '-' + @year

To test that, replace mydate with an actual date (2007-06-05 for example)

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

Dang! Wrong again. That's what I get for having all my dates as DATETIME, I never have to convert date strings. I tried the solutions given by everyone BUT me and sure enough, the value doesn't have to be DATETIME to use convert.

Oh well.................I've been wrong before and I'll be wrong again. I just hope I'm RIGHT more than wrong.

-SQLBill

Posting advice: FAQ481-4875