How to replace an int with text in a query

Question

I have this query, which returns days of the week. The days of the week are 0-6(Mon-Fri).

select ID, DayOfWeek from MyTable

ID    DayOfWeek
1     0
2     0
3     4

Is there a way to substitute an alias for each int DayOfWeek? The enum isn't stored in the database so I can't join.

I want to be able to write a query and have it look like this:

ID    DayOfWeek
1     Monday
2     Monday
3     Friday

This is the opposite question, but the solutions still apply: http://stackoverflow.com/questions/387434/converting-the-name-of-a-day-to-its-integer-representation — Fiona - myaccessible.website, Aug 07 '13 at 15:35
You should have a lookup table in your database for this. If you need it as an enum, it can be autogenerated using some templating mechanism (for example T4). — OzrenTkalcecKrznaric, Aug 07 '13 at 15:36

Michael Fredrickson · Accepted Answer · 2013-08-07T15:42:09.650

8

You should consider storing the lookup in a new table... but just so you're aware of your options, you can also use the DATENAME(WEEKDAY) function:

SELECT DATENAME(WEEKDAY, 0)

Returns:

Monday

edited Aug 07 '13 at 15:42

answered Aug 07 '13 at 15:37

Curious. Why does this return monday? – lc. Aug 07 '13 at 15:41
Answered my own question. Apparently `0` is `January, 01 1900 00:00:00+0000`, which just *happens* to be a Monday. Love it. – lc. Aug 07 '13 at 15:43
1

@lc. Dumb luck. `CAST(0 AS DATETIME)` is January 1st, 1900... which happened to be a Monday. – Michael Fredrickson Aug 07 '13 at 15:43

score 2 · Answer 2 · answered Aug 07 '13 at 15:35

2

If you can't store a table to join on, you can use a CASE:

SELECT ID,
    CASE DayOfWeek
        WHEN 0 THEN 'Monday'
        WHEN 1 THEN 'Tuesday'
        ...
    END AS DayOfWeek
FROM MyTable

answered Aug 07 '13 at 15:35

lc.

score 2 · Answer 3 · answered Aug 07 '13 at 15:36

You can try following:

SELECT ID, DATENAME(dw, DATEADD(d, DayOfWeek, CAST('2013-08-05' AS date)))
FROM MyTable

The logic is:

Edit

Or even simpler way: DATENAME(dw, DayOfWeek): Live demo

score 1 · Answer 4 · answered Aug 07 '13 at 15:37

1

datename(weekday, DayOfWeek)

should translate the int to a name.

answered Aug 07 '13 at 15:37

Ryan

4 Answers4