Re: To insert Dates in a particular format (with day of the week) into a table.

Building on Art's fine answer if you need to do this, then the database as the to_char function built in: select to_char(current, "%a") from systables where tabid = 1; select to_char(current, "%A") from systables where tabid = 1; So the database already does this for you and the nice thing is that you will get the answer in the current locale so now you have an international product. ;-) If you have to build a table then use the same function to get the results that you need Here is the excerpt from the FM, since you probably don't have access to one. ;-) SELECT TO_CHAR(begin_date, '%A %B %d, %Y %R') FROM tab1 The symbols in the format_string parameter in this example have the following meanings. For a complete list of format symbols and their meanings, see the GL_DATE and GL_DATETIME environment variables in the IBM Informix: GLS User's Guide. Symbol Meaning %A Full weekday name as defined in the locale %B Full month name as defined in the locale %d Day of the month as a decimal number %Y Year as a 4-digit decimal number %R Time in 24-hour notation The result of applying the specified format_string to the begin_date column is as follows: Wednesday July 23, 1997 18:45 TO_DATE Function (IDS): The TO_DATE function converts a character string to a DATETIME value. The function evaluates the char_expression parameter as a date according to the date format you specify in the format_string parameter and returns the equivalent date. If char_expression is NULL, then a NULL value is returned. Any argument to the TO_DATE function must be of a built-in data type. If you omit the format_string parameter, the TO_DATE function applies the default DATETIME format to the DATETIME value. The default DATETIME format is specified by the GL_DATETIME environment variable. Here is the link for the manuals in case you have access to the internet: http://www-306.ibm.com/software/data/informix/pubs/library/ I find the SQL reference manual to be very helpful in these situations. mc wrote: > Hi, > I need to insert all the dates in 2007-2009 into a table in the > following format. > > > calendar_date 20070101 > Day_of_week MON > > The column "calendar_dt" is not of type date, but character.

Ooops my answer isn't quite right. > select to_char(current, "%a") from systables where tabid = 1; > select to_char(current, "%A") from systables where tabid = 1; select upper(to_char(current, "%a")) from systables where tabid = 1; select upper(to_char(current, "%A")) from systables where tabid = 1; I hope this cause any problems. ;-) bozon wrote: > Building on Art's fine answer if you need to do this, then the database > as the to_char function built in: > > select to_char(current, "%a") from systables where tabid = 1; > select to_char(current, "%A") from systables where tabid = 1; > > So the database already does this for you and the nice thing is that > you will get the answer in the current locale so now you have an > international product. ;-) If you have to build a table then use the > same function to get the results that you need > > Here is the excerpt from the FM, since you probably don't have access > to one. ;-) > > SELECT TO_CHAR(begin_date, '%A %B %d, %Y %R') FROM tab1 > The symbols in the format_string parameter in this example have the > following meanings. For a complete list of format symbols and their > meanings, see the GL_DATE and GL_DATETIME environment variables in the > IBM Informix: GLS User's Guide. Symbol Meaning %A Full weekday name as > defined in the locale %B Full month name as defined in the locale %d > Day of the month as a decimal number %Y Year as a 4-digit decimal > number %R Time in 24-hour notation The result of applying the specified > format_string to the begin_date column is as follows: Wednesday July > 23, 1997 18:45 TO_DATE Function (IDS): The TO_DATE function converts a > character string to a DATETIME value. The function evaluates the > char_expression parameter as a date according to the date format you > specify in the format_string parameter and returns the equivalent date. > If char_expression is NULL, then a NULL value is returned. Any argument > to the TO_DATE function must be of a built-in data type. If you omit > the format_string parameter, the TO_DATE function applies the default > DATETIME format to the DATETIME value. The default DATETIME format is > specified by the GL_DATETIME environment variable. > > Here is the link for the manuals in case you have access to the > internet: > > http://www-306.ibm.com/software/data/informix/pubs/library/ > > I find the SQL reference manual to be very helpful in these situations. > > > mc wrote: > > Hi, > > I need to insert all the dates in 2007-2009 into a table in the > > following format. > > > > > > calendar_date 20070101 > > Day_of_week MON > > > > The column "calendar_dt" is not of type date, but character.