asked on

Basic SQL - Date

I have a numeric field containing a date formated as CYYMMDD(C = Century, if C=1 it is 2000, if C=0 it is 1900). Some dates are :-
1040915
1040915
1040915
1040917
1040917
1040428

I want t display it as MM-DD-YYYY.
This works for me but I feel the formating is too long.

select CONVERT(CHAR(10), CONVERT(DATETIME, SUBSTRING(LTRIM(STR(A.DateCreated)),2,6), 112), 110) 'Date Created'
from Order A

some result
09-15-2004
09-15-2004
09-15-2004
09-15-2004
09-15-2004
04-28-2004
08-18-2004
08-18-2004
08-18-2004
09-15-2004
09-15-2004

If you have a better Approach let me. If you think I am doing the right thing let me know too. I will award the points either way.

Thanks

ASKER CERTIFIED SOLUTION

SashP

membership

This solution is only available to members.

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

Start Free Trial

SOLUTION

Hilaire

membership

This solution is only available to members.

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

Start Free Trial

SashP

Your function does not work when the century = 0.

So "if it aint broke don't fix it" does not seem to apply here.

Have a look at what happens when you get dates in the 1900s

declare @a table (adate int)

insert into @a values('1040915')
insert into @a values('1990915')
insert into @a values('1040915')
insert into @a values('1040917')
insert into @a values('0000917')
insert into @a values('0990421')

select * from @a

select STR(adate) from @a

SashP

Then look at

declare @a table (adate int)

insert into @a values('1040915')
insert into @a values('1990915')
insert into @a values('1040915')
insert into @a values('1040917')
insert into @a values('0000917')
insert into @a values('0990421')

select * from @a

select case when adate > 999999 then '20' else '19' end + right('000' + ltrim(STR(adate)),6) from @a