All Forums Tools
pmoe33 3 posts Joined 05/06
26 May 2006
Date Comparisions - Differing formats

My database uses the date format yyyy-mm-dd. I have a new data source that uses yyyymm for a key date. How can I compare the two? I need to essentially do this:whereyyyymm between yyyy-mm-dd1 and yyyy-mm-dd2

dnoeth 4628 posts Joined 11/04
26 May 2006

Just specify a matching format, if the day is missing it will be replaced by the 1st.where '200605' (date, format 'yyyymm') between date1 and date2Dieter

Dieter

pmoe33 3 posts Joined 05/06
26 May 2006

thanks!

pmoe33 3 posts Joined 05/06
01 Jun 2006

I'm not getting results back from this. SELECT SOURCE_MONTHYEARWHERESOURCE_MONTHYEAR (date, format 'yyyy/mm/dd') BETWEEN VARIABLE1 AND VARIABLE2VARIABLE1 and VARIABLE2 are date format YYYY/MM/DDSOURCE_MONTHYEAR is date format YYYYMMI did get results using substring and concatenation on the between variables, i.e.: SUBSTR(VARIABLE1,1,4) || SUBSTR(VARIABLE1,5,2)I've tried using date, format yyyymm on the between variables as well with empty results.What else can I try?Thanks in advance

Fred 1096 posts Joined 08/04
02 Jun 2006

I think you are confusing external and internal representation. Is SOURCE_MONTHYEAR actually defined as DATE or is it CHAR that you know is supposed to represent a YYYYMM format date? The FORMAT specification is used for the conversion between external (character string) and internal datatypes (such as DATE). If you aren't doing conversion to/from character, FORMAT isn't relevant.If SOURCE_MONTHYEAR is a character string, try it the way Dieter suggested.On the other hand, if VARIABLE1 and VARIABLE2 are actually character strings you'd want to CAST those.In either case, the FORMAT describes the character string - not the comparison.

Fred 1096 posts Joined 08/04
02 Jun 2006

I think you are confusing external and internal representation. Is SOURCE_MONTHYEAR actually defined as DATE or is it CHAR that you know is supposed to represent a YYYYMM format date? The FORMAT specification is used for the conversion between external (character string) and internal datatypes (such as DATE). If you aren't doing conversion to/from character, FORMAT isn't relevant.If SOURCE_MONTHYEAR is a character string, try it the way Dieter suggested.On the other hand, if VARIABLE1 and VARIABLE2 are actually character strings you'd want to CAST those.In either case, the FORMAT describes the character string - not the comparison.

You must sign in to leave a comment.