How to reformat and sort dates stored in nvarchar

Question

I am trying to display Month and YY part of the date column such that it should appear as

Original Format

Apr 12 2009;          
Feb 20 2009;
Dec 24 2008;
May 18 2009;
Jan  8 2009;
Dec  6 2008;
Apr 19 2009;
Jan  4 2009;
May 13 2009;
Jan  5 2009

From these dates the day part should be ripped off such that it appears as "Apr 09" and can still be sorted by date such that it appears as:

Dec 08;
Jan 09;
Feb 09;
Mar 09;
Apr 09;
May 09

Thanks, Sag.

Answer 1

You need the LEFT function, and you can sort datetime or smalldatetime in SQL SERVER.

Answer 2

you don't say which database, this is for SQL Server... try:

declare @x table (datestring nvarchar(30))
insert into @x values ('Feb 20 2009')
insert into @x values ('Dec 24 2008')
insert into @x values ('May 18 2009')
insert into @x values ('Jan 8 2009')
insert into @x values ('Dec 6 2008')

select * from @x

SELECT
    LEFT(CONVERT(varchar(30),convert(datetime,datestring),109),3)+' '+SUBSTRING(CONVERT(varchar(30),convert(datetime,datestring),121),3,2)
    FROM @x
    ORDER BY CONVERT(datetime,datestring)

You should not store dates in strings, it takes more space, and makes it difficult to sort and compare them.

Answer 3

If you want to store the dates as strings in the datbase, you need to use a format that builds on the same principle as the ISO-8601 standard, i.e. you use numerical months and place the most significant value first.

To be textually sortable your values would need to look something like:

08-12, 09-01, 09-02, 09-03, 09-04, 09-05

However, I suggest that you store the dates as datetime values in the database instead, and take care of the formatting when you display the values. That way you can easily sort them in the database (and also do any other processing like getting the difference between two dates).

From a datetime value you can quite easily get the format you want. In .NET for example you use theDate.ToString("MMM yy") to format the date exactly as you want it. As you do the formatting after getting the data from the database it's not a problem that the format is not textually sortable.