Hello,

I am quite new to SQL and Unix so I am having a problem with what may be thought of as a simple problem.

I have a table (name=tdb.tb_rf_day) which has a date column using the following format,
"2006-01-01-00.00.00.000000"

I would like to query the table to display the output as the day of the week (Example - 2006-01-01-00.00.00.000000 would display as "Sunday")

Here's the hard part, I have an existing query pulling other information out of the table as well and I would like to do everything in 1 query if possible.

Query example -
The second reference (TDB.TB_RF_DAY.DATETIME AS DAY_OF_THE_WEEK) to the DATETIME field is where I would like to have my conversion done.

Thanks in advance.

unable to understand your problem
anyways there is a function - dayname

C:\Program Files\IBM\SQLLIB\BIN>db2 values dayname('2006-01-01-00.00.00.000000')

1
------------
Sunday

--Rahul

Rahul's solution works because he is converting the timestamp data type into the dayname format. Spanky1968, it appears that you think that the "AS DATE" will do the conversion. This will simply name the column returned as "DATE". This is referred to as aliasing a column name.

You need to use funtions to convert data as Rahul shows in hist post.

I suggest you download the DB2 UDB V8 SQL Cookbook by Graeme Birchall. Search for it on Google. The V7.2 or V8.2 are just as good for generic SQL tutorials. This free book will help give you the background to get started.

-- Steve

On z/OS you won't find the Dayname function; in that case use the Dayofweek function (returning a number between 1 and 7) and convert it to text with a CASE expression.