All Forums Database
samit_9999 37 posts Joined 03/06
04 Sep 2007
Character to Date & Date Difference

Hi,I need to convert a character field(Field A) to a date field and then subtract it with a another date field(Field B) which is in date formatField A - 060725 to be converted as '2006-07-25'Field B - 2006-05-18When i cast Field A as date format 'yymmdd' i get an output as 07/25/1906 instead of 07/25/2006If there is any other way i can achieve the desired results please let me know.Help Please.Thanks Much in AdvanceSam

Jim Chapman 449 posts Joined 09/04
04 Sep 2007

The system parameter called "Century Break" determines how the system interprets a 2-digit year. The default setting is 0, which causes all year values in 'yy' format to be converted to '19yy'. If Century Break is set to 50, then years less than 50 are converted to '20yy'. Set Century Break to 100 if you want a 2-digit year always to be interpreted as '20yy'.For more information, see the Utilities reference manual, volume 1, chapter 11 (DBS Control).If your system administrator doesn't agree that the Century Break parameter for your system should be changed, another possible solution is to manipulate the input string. Here is a demonstration:select cast('20'||'06-07-25' as date) (format 'yyyy-mm-dd') as mydate; *** Query completed. One row found. One column returned. *** Total elapsed time was 1 second. mydate----------2006-07-25

You must sign in to leave a comment.