How to convert/change data in database temporarily with SQL?

Question

I am currently making a timetable using the daypilot scheduler. My current start and end data fields are set to the class start period and end period of the timetable, which are just integers from 1 to 9. But because the daypilot scheduler takes the data in a dateTime format, I must change the class period numbers into time and the days, which are currently "mon, tues, weds..." to dates..

Is there anyway to change the data temporarily so that when I query the database, the result I get will be time for the periods and dates for the days?

Here's my attempt:

SELECT * FROM
(
(SELECT requestID, CASE day WHEN 'Monday' THEN '1' WHEN 'Tuesday' THEN '2' WHEN 'Wednesday' THEN '3' ELSE day END AS dayA FROM request) AS a
INNER JOIN
(SELECT requestID, CASE day WHEN 'Monday' THEN '1' WHEN 'Tuesday' THEN '2' WHEN 'Wednesday' THEN '3' ELSE day END AS dayB FROM request) AS b
ON a.requestID = b.requestID
)
WHERE a.dayA < b.dayB

I took this from Change SQL data temporarily, but it didn't work out for me and it only produced empty data. Where am I going wrong, and can anyone suggest how I can do this?

can you post some sample data? does your day field contain `'Monday'` or `'1'`? — ughai, May 04 '15 at 04:41
Can't you add both columns in your table, or have a reference table to change `id`s to time and days? — Sébastien Sevrin, May 04 '15 at 07:30
The field contains Monday, Tuesday, etc. and I want to conver those days into numbers temporarily. I don't want to have to add more columns to the table. — jellybean_232, May 04 '15 at 10:34

score 0 · Accepted Answer · answered May 04 '15 at 14:02

I've figured how to do this... here's my generic solution to convert data temporarily in a column of a table:

SELECT ID, Name, 
CASE day WHEN 'Monday' THEN '1' 
WHEN 'Tuesday' THEN '2' 
WHEN 'Wednesday' THEN '3' 
WHEN 'Thursday' THEN '4' 
WHEN 'Friday' THEN '5' 
ELSE day END AS day FROM Table

How to convert/change data in database temporarily with SQL?

1 Answers1