sorting dates stored as strings

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

sorting dates stored as strings

Author

Topic

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2011-05-04 : 17:41:37

Hi

we've some ancient data where dates are stored in a char field (e.g. May 2009,Apr 2010 etc.,) . as you'd expect when we show this data in a front end application the data shown in random order (as you'd expect its string field not date). my question is , is there any way to sort this as date (i mean May 2009 comes before apr 2010) ?

Thanks for the advise.

Cheers

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-04 : 17:58:14

Can you change that to a datetime column? Failing that, can you at least update them to a consistent date format, like YYYYMMDD?

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2011-05-04 : 18:02:46

cant change that am afraid. need to change how its displayed, any ideas ? thanks

Cheers

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2011-05-04 : 18:04:24

DAY is not important in this scenario thats why original developer used a char field and stored month & year and did not realise display problems.

Cheers

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-05-04 : 19:17:46

Are these dates always formatted as MMM YYYY (Apr 2010, May 2009)? Is it possible that you might have different formats?

If not, you can convert that to a datetime column either in your query or in a computed column on the table. To convert to a datetime, you would use the following:


Select convert(datetime, 'Apr 2010', 107);
Select convert(datetime, 'Mar 2009', 107);
Select convert(datetime, 'Jan 2011', 107);
Select convert(datetime, 'Jun 2010', 107);

If you have different formats, you need to identify all of the various formats and structure them (if needed) into a valid date format that can be converted to a datetime.

Jeff

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2011-05-04 : 19:26:55

yes dates are always formatted as MMM YYYY. Thank you very much Jeff your sql works beautifully.

Cheers

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2011-05-04 : 19:33:23

btw i previously checked "convert" function but did not see any number for format MMM YYYY! the one you used (107) as per help file its "Mon dd, yy" but still works though my input is not in that format!

does this work for all different datetime settings on the client systems? thanks

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources