How to convert 1,2,3 to Sunday, Monday, Tuesday...

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 2005 Forums

Transact-SQL (2005)

How to convert 1,2,3 to Sunday, Monday, Tuesday...

Author

Topic

pacolim
Starting Member

11 Posts

Posted - 2010-05-21 : 02:11:43

How to convert 1,2,3 to Sunday, Monday, Tuesday...? Not converting from date, it's converting from number to weekday.
Is there any built in function available? Or I will need to manual define?

Thank you

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-05-21 : 02:32:44

Use Case

Select Case when column_name=1 then 'sunday'
when column_name=2 'monday'.....
else 'saturday' end from table_name

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

pacolim
Starting Member

11 Posts

Posted - 2010-05-21 : 02:57:53

but this is kinda manual define, no built in function in sql that can do the conversion?

waterduck
Aged Yak Warrior

982 Posts

Posted - 2010-05-21 : 03:08:04

this might look ugly
declare @a int
set @a = 1
select datename(dw, '1900010'+cast(@int as char))

Hope can help...but advise to wait pros with confirmation...

pacolim
Starting Member

11 Posts

Posted - 2010-05-21 : 03:55:23

thanks for all the help, I think waterduck solution is shorter, although it seems like tricky but it did get the result I want. :)

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2010-05-21 : 15:57:29

declare @a integer
set @a = 12
select datename(dw, '1900010'+cast(@a as char))

If you try two digit number it would failed.

You would get the below error..

Msg 241, Level 16, State 1, Line 3
Conversion failed when converting datetime from character string.

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2010-05-21 : 17:24:05

@sqlfresher2k7: There are only 7 days in a week

Even still, why concatenate a string when you can just do this:

select NUMBER, datename(dw,NUMBER-2)
FROM
(
SELECT 1 NUMBER UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9
) as z

/*

NUMBER (No column name)
1 Sunday
3 Tuesday
4 Wednesday
5 Thursday
6 Friday
7 Saturday
8 Sunday
9 Monday
--*/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources