Order by Day on an nvarchar

Question

Post reply

Order by Day on an nvarchar

Dave Mateer

Grasshopper

Points: 23
More actions
January 23, 2005 at 10:23 pm

#162274

Hi
I have an datatype nvarchar column containing:
Friday
Monday
Wednesday
Tuesday
etc..
Can I ORDER BY this column as a day of the week (whereby the Monday would come first and Sunday would be last).
I have considered altering the datatype to date.. however this seems like overkill!
I am using SQL Server 2000... If anyone could suggest a good tutorial site, or book to buy for SQL Server 2k I would be much obliged. It is tough learning this db as it is more powerful than MySQL/Aceess which I've used before.
Kind Regards
Dave.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47795 More actions · Answer 1

The only way you could get SQL Server to use date sorting is to convert the column to datetime.

Other options would be

a) add a new field to hold an integer value for the day number eg: 1=Monday, 2=Tuesday,etc...

b) add the number to the start of the values when storing them and strip them off when displaying in the front-end, eg 1Monday, 2Tuesday, etc...

Also, grab a copy of Professional SQL Server 2000 Programming by Robert Vieira

--------------------
Colt 45 - the original point and click interface

Dave Mateer Grasshopper Points: 23 More actions · Answer 2

Hi Phill

Many thanks for the reply. Yep - I like the idea of using a simple number to sort on.. dates can get way messy.

Nice one on the book... I'm in NZ (I see you're in Oz somewhere?).. always a damn hasstle to get books in Middle Earth.

Cheers

Dave.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

If you don't want to (or have the possibility to) change the schema, you could order by a CASE statement.

select * from days

order by case dayname

when 'Monday' then 1

when 'Tuesday' then 2

when 'Wednesday' then 3

when 'Thursday' then 4

when 'Friday' then 5

when 'Saturday' then 6

when 'Sunday' then 7

end

But, it would probably be best if it was possible to use a 'real' datetime to do your date arithmetics on.

/Kenneth

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

As has been already said, when you have a date stored in a DATETIME column, you are always able to use SQL Server's built-in functionality to get the dayname associated with the date in question. Hm, this might be overkill here, but have a look at Rick Snodgrass homepage here http://www.cs.arizona.edu/people/rts/ You can download his classic book on time oriented application in SQL there for free.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Dave Mateer Grasshopper Points: 23 More actions · Answer 5

Frank and Kenneth

Many thanks for the tips. The Rick Snodgrass book is some serious stuff!

Kind Regards

Dave.

Calvin Lawson SSChampion Points: 11030 More actions · Answer 6

Nice one, Joe...I actually had to run this before I understood what you were doing. A very subtle use of charindex.

create table Temp_DAY (dt varchar(20))

Insert Temp_DAY values ('Monday')

Insert Temp_DAY values ('Tuesday')

Insert Temp_DAY values ('Wednesday')

Insert Temp_DAY values ('Thursday')

Insert Temp_DAY values ('Friday')

Insert Temp_DAY values ('Saturday')

Insert Temp_DAY values ('Sunday')

select *

from Temp_DAY

ORDER BY CHARINDEX (dt, 'MondayTuesdayWednesdayThursdayFridaySaturdaySunday')

drop table Temp_Day

Signature is NULL