|
|
|
Forum Newbie
Group: Forum Members
Last Login: 6/1/2006 12:25:00 PM
Posts: 3,
Visits: 1
|
|
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:
where
yyyymm between yyyy-mm-dd1 and yyyy-mm-dd2
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 11/5/2009 4:40:02 PM
Posts: 717,
Visits: 466
|
|
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 date2
Dieter
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 6/1/2006 12:25:00 PM
Posts: 3,
Visits: 1
|
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 6/1/2006 12:25:00 PM
Posts: 3,
Visits: 1
|
|
I'm not getting results back from this.
SELECT SOURCE_MONTHYEAR
WHERE
SOURCE_MONTHYEAR (date, format 'yyyy/mm/dd') BETWEEN
VARIABLE1
AND VARIABLE2
VARIABLE1 and VARIABLE2 are date format YYYY/MM/DD
SOURCE_MONTHYEAR is date format YYYYMM
I 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
|
|
|
|
|
Supreme Being
Group: PAC and SFT Members
Last Login: 9/9/2009 4:34:06 PM
Posts: 407,
Visits: 889
|
|
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.
|
|
|
|
|
Supreme Being
Group: PAC and SFT Members
Last Login: 9/9/2009 4:34:06 PM
Posts: 407,
Visits: 889
|
|
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.
|
|
|
|