compare longs instead of dates ?

[cbsm]

I am working in an environment where some users need to have American dates (mm/dd/yyyy), other european dates (dd/mm/yyyy).
The day is not important here (always first day of the month).
To compare two dates, I decided to get the year part, and the month mart - put them together : YYYYMM.
Now, I have a date field Date1, that I like to compare with the other "date" put in a long variable :
Select * from Table1 WHERE clng(format([date1],'yyyymm')) > 200512;
I get the error : Data mismatch in the criteria expression.
(For info if I do Select clng(format([date1],'yyyymm')) from Table1 it works just fine
Or Select * from Table1 WHERE format([date1],'yyyymm') > '200512' (but I need a number comparison, not string).

 

[PHV]

Perhaps this ?
SELECT * FROM Table1 WHERE Int(Format([date1],'yyyymm')) > 200512

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

In fact I guess you have some Null values in date1 ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cbsm]

Nope - No null values in date1 (to be sure, I based this not on a table, but on a query which excludes the Null values).

I tried Cint : result Overflow.
Again very strange :
If I do a simple Select cint(format([date1],'yyyymm')) from table1
It works fine.
If I do Select * from table1 where cint(format([date1],'yyyymm')) = 200512 - Overflow error.

 

[PHV]

I didn't suggested CInt but Int.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cbsm]

Sorry !

But still does not work ...
Same message as Cint : overflow !

I tried this :
I've create a query QUERY 1 :
Select CInt(Format([Date1],'yyyymm')) as Date1Formated from table1.

This query works fine.

But then if I do

SELECT * from QUERY1 where Date1Formated =200512
I still got the errors as above (Overflow or type mismatch depending if I use Cint,int or Clng).
I also tried SELECT * from QUERY1 where Date1Formated ='200512' - even if I do not want this - but I've got an type mismatch also !

I really do not understand !!!!

 

[cbsm]

Apparently this was a null issue afterall ...
Even If I do not understand how ...(as I said, I look for not-Null values in date1 - and this works in the select statement).
I added a iif(isnull etc...) and it worked.

A while a go I always wanted to understand why something was working or not ... unfortunatelly I do not have the time and courage to do so now ... I am just happy to make it work ...

Many thanks for the time, and the inspiration !

 

[PHV]

but I need a number comparison, not string
Why not ? yyyymm is safe for lexicographic sort or comparison:
SELECT * FROM Table1 WHERE Format([date1],'yyyymm') > '200512'


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886