By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,608 Members | 1,925 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,608 IT Pros & Developers. It's quick & easy.

DateTime comparison problem when use format() or dateValue()

P: 51
In our database, the publication year and month are saved in different columns, the query is to find out the pubs after 7/1/2006. My following query works but not get the desired dataset, e.g. publications during 10/1/2006 to 12/31/2006 was not listed in the result. I am frustrated, please help! Thanks very much.
Expand|Select|Wrap|Line Numbers
  1. SELECT Publications.PubYear, Publications.PubMonth, format(mid(PubMonth,1,3) & ' 1,' & PubYear, 'short date') AS Expr2,mid(PubMonth,1,3), IsDate(DateValue(format(mid(PubMonth,1,3) & ' 1,' & PubYear, 'short date')))
  2.  
  3. FROM [select * from publications where PubYear is not null and pubMonth is not null and (pubyear=2006 or pubyear=2007)]. as Publications
  4. where format(mid(PubMonth,1,3) & ' 1,' & PubYear, 'short date') >= #2006-07-01#
PS.
I tried to use DataValue(), but it always gives 'Datatype mismatch..."error (there's no null value, all values seem are date type for me).
Feb 19 '08 #1
Share this Question
Share on Google+
4 Replies


Expert Mod 2.5K+
P: 2,545
In our database, the publication year and month are saved in different columns, the query is to find out the pubs after 7/1/2006. ... SELECT Publications.PubYear, Publications.PubMonth, format(mid(PubMonth,1,3) & ' 1,' & PubYear, 'short date') AS Expr2,mid(PubMonth,1,3), IsDate(DateValue(format(mid(PubMonth,1,3) & ' 1,' & PubYear, 'short date')))

FROM [select * from publications where PubYear is not null and pubMonth is not null and (pubyear=2006 or pubyear=2007)]. as Publications
where format(mid(PubMonth,1,3) & ' 1,' & PubYear, 'short date') >= #2006-07-01#
Hi Anniebai. The use of Format is not helping you here. Format returns a string representation of a date, not a date as it is internally stored (as a number). You are also using a subquery, which seems a lot for a simple query.
As you already hold the year and month values as discrete columns within your publication table (assuming these are integers), you could compare these directly with the year and month you need to find:
Expand|Select|Wrap|Line Numbers
  1.  
  2. Select * from publications where (PubYear >= 2006) AND (PubMonth>=7);
  3.  
If the PubYear and PubMonth values are text rather than Integers or Longs you can convert them to integers using the CInt function (e.g. where (CInt(PubYear) > 2006)...

If you do need to convert your publication year and month to dates, use DateSerial instead of Format:
Expand|Select|Wrap|Line Numbers
  1.  
  2. Select * from publications where (DateSerial(PubYear, PubMonth, 1) >= #07/01/2006#; 
  3.  
Note that the standard Access format of a date value given inside the hashes is mm/dd/yyyy, not yyyy-mm-dd as in your SQL.

Hope this helps.

-Stewart
Feb 19 '08 #2

NeoPa
Expert Mod 15k+
P: 31,769
You can either convert your columns to a number, a string or a date. As you're using manipulation anyway, I'd go for the number. You need to compare the whole item in one go for best results as there are situations where the month can actually be LESS than the required month (if the year is greater). BTW I'm assuming you're meaning ON OR after 1 July 2006.
Expand|Select|Wrap|Line Numbers
  1. WHERE ([PubYear]*100+[PubMonth])>200606
Feb 20 '08 #3

Expert Mod 2.5K+
P: 2,545
Oops! Error in my first reply in the line
Expand|Select|Wrap|Line Numbers
  1.  
  2. Select * from publications where (PubYear >= 2006) AND (PubMonth>=7);
  3.  
This condition is in error as it selects only those months from July onwards. Neopa's combination of year and month is more elegant!

A corrected version of the SQL statement is
Expand|Select|Wrap|Line Numbers
  1. Select * from publications where (PubYear > 2006) Or ((PubYear = 2006) AND (PubMonth>=7));
Anyway, NeoPa's solution is simpler and more elegant...

-Stewart
Feb 20 '08 #4

NeoPa
Expert Mod 15k+
P: 31,769
...Anyway, NeoPa's solution is simpler and more elegant...

-Stewart
Thanks Stewart. I could hardly ask for a higher compliment :)
Feb 20 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.