Character to Date & Date Difference

Database
Enthusiast

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 format

Field A - 060725 to be converted as '2006-07-25'
Field B - 2006-05-18

When i cast Field A as date format 'yymmdd' i get an output as 07/25/1906 instead of 07/25/2006

If there is any other way i can achieve the desired results please let me know.

Help Please.

Thanks Much in Advance

Sam
1 REPLY
Enthusiast

Re: Character to Date & Date Difference

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