SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 sorting dates stored as strings
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 05/04/2011 :  17:41:37  Show Profile  Reply with Quote
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

USA
15679 Posts

Posted - 05/04/2011 :  17:58:14  Show Profile  Visit robvolk's Homepage  Reply with Quote
Can you change that to a datetime column? Failing that, can you at least update them to a consistent date format, like YYYYMMDD?
Go to Top of Page

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 05/04/2011 :  18:02:46  Show Profile  Reply with Quote
cant change that am afraid. need to change how its displayed, any ideas ? thanks

Cheers
Go to Top of Page

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 05/04/2011 :  18:04:24  Show Profile  Reply with Quote
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
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
806 Posts

Posted - 05/04/2011 :  19:17:46  Show Profile  Reply with Quote
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

Go to Top of Page

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 05/04/2011 :  19:26:55  Show Profile  Reply with Quote
yes dates are always formatted as MMM YYYY. Thank you very much Jeff your sql works beautifully.

Cheers
Go to Top of Page

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 05/04/2011 :  19:33:23  Show Profile  Reply with Quote
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

Edited by - rajani on 05/04/2011 19:35:12
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000