Thomas: I'm aware of the to_char function and the various options. However as you alluded to, that lands in SAS as a character literal, e.g. '22/01/2004' requiring me to convert it to internal format before I can use it in SAS.
I think I have to use my work around: select date_fld - to_date('01Jan1960','ddmonyyyy') as sas_date Thanks for your time and help. -----Original Message----- Sent: January 23, 2004 3:32 PM To: '[EMAIL PROTECTED]' Cc: Droogendyk, Harry Harry, Look at the to_char function in Oracle. It will convert a date field to *any* format you want. for example: select to_char(date_field,'mm/dd/yyyy hh24miss') will return a date in the format as noted. You have about as many options as you probably need. You can combine as many format and functions as you think you need. to_char translates dates to chars and to_date does the opposite - but both use the same format statements. This should work fine for you. And for you info - Oracle dates are stored internally based on a date going back many centuries - not just to 1968! Good Luck Tom Mercadante Oracle Certified Professional -----Original Message----- Sent: Friday, January 23, 2004 3:09 PM To: Multiple recipients of list ORACLE-L Thomas: I'm a SAS guy who must pull Oracle data from the back-end DB. SAS stores dates internally as elapsed days since Jan 1, 1960. If I request an Oracle date field, SAS creates a datetime variable, number of seconds since midnight Jan 1, 1960. Rather than use SAS functions to extract the date ( e.g. datepart function ), I'd like to push that back to Oracle if possible and create a simple date field on SAS. I can accomplish the desired effect with: select date_fld - to_date('01Jan1960','ddmonyyyy') as sas_date but I was hoping there was an Oracle function to surface the internal value ( appears to be days since Jan 1, 1968 ). Presumably such a function would be more efficient. Thanks for your help. -----Original Message----- Sent: January 23, 2004 2:50 PM To: Multiple recipients of list ORACLE-L Harry, Can you explain why you need to raw internal value? Just curious. Tom Mercadante Oracle Certified Professional -----Original Message----- Sent: Friday, January 23, 2004 1:40 PM To: Multiple recipients of list ORACLE-L You gather it wrong :) Oracle stores date in 8 bytes, one for each: year, month, day, hour, min, ... etc. Igor Neyman, OCP DBA [EMAIL PROTECTED] -----Original Message----- Droogendyk, Harry Sent: Friday, January 23, 2004 12:40 PM To: Multiple recipients of list ORACLE-L Folks: >From what I gather, Oracle stores dates as the number of elapsed days since Jan 1, 1968. When I query a column of type 'DATE', it returns me the default format, dd-mon-yy. I know I can use to_char(date_col,'YYYYMMDD') etc... to define many output formats. What can I do to get the raw internal value of the date? i.e. today is 13172. Thanks. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Droogendyk, Harry INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Droogendyk, Harry INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Droogendyk, Harry INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).