| Author |
Topic |
|
Lyriclover
Starting Member
8 Posts |
 Posted - 2010-02-03 : 17:57:24
|
| I'm relatively new to SQL, and have always seemed to have trouble with date related issues. Below is the query part of the code I'm using to spit out a table on our website. The data appears in the table fine, except the fact that all Feb data appears (my data goes back to 1995)."SELECT Date, Feb FROM ChartPrice where Date > 12/31/2005 AND Feb > 0 order by Date;" I've tried to solve my problem, using '12/31/2005', #12/31/2005#, in all sorts of orders and formats, but nothing works! The dates in my access table are formatted as medium date (as is the case with all our data tables - therefore 31-Dec-2005 formatted). Any help you could provide would be greatly appreciated! |
|
|
melon.melon
Yak Posting Veteran
76 Posts |
Posted - 2010-02-03 : 21:34:33
|
| Hi Lyriclover,Are u trying to get the all the date after 12/31/2005 inclusive of all the months or would u want to get the date after 12/31/2005 that provide only Feb dates ? |
 |
|
|
rajpes
Starting Member
13 Posts |
Posted - 2010-02-03 : 23:45:38
|
| SELECT Date, Feb FROM ChartPrice where Date > '12/31/2005' AND Feb > 0 order by Date--dont forget using quotes! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-04 : 03:28:39
|
| i would prefer passing dates in CCYY-MM-DD HH:MI:SS format |
|
|
|
Lyriclover
Starting Member
8 Posts |
Posted - 2010-02-04 : 11:15:34
|
When I tried this format, I received the following error:Microsoft JET Database Engine error '80040e07' Data type mismatch in criteria expression. /hf.asp, line 77 :S Any suggestions?quote:
Originally posted by rajpes
SELECT Date, Feb FROM ChartPrice where Date > '12/31/2005' AND Feb > 0 order by Date--dont forget using quotes!
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-04 : 11:18:39
|
| try passing it in format i specified |
|
|
|
Lyriclover
Starting Member
8 Posts |
Posted - 2010-02-04 : 11:19:18
|
My apologies, I will clarify. I'm looking to query from my table, all days after Dec 31'05 (basically, I just want 2006 on). This data represents each day of the week, and the Feb column contains a futures contract (therefore, Feb column will always contain data, no matter which month we're looking at).Hope that clarifies my question. Thanks,quote:
Originally posted by melon.melon
Hi Lyriclover,Are u trying to get the all the date after 12/31/2005 inclusive of all the months or would u want to get the date after 12/31/2005 that provide only Feb dates ?
|
|
|
|
Lyriclover
Starting Member
8 Posts |
Posted - 2010-02-04 : 11:22:13
|
Just tried it now, I had to close the code with #'s, but it works great! Thanks! :DSELECT Date, Feb FROM ChartPrice where Date > #2005/12/31 00:00:00# AND Feb > 0 order by Datequote:
Originally posted by visakh16
try passing it in format i specified
|
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-02-04 : 11:22:19
|
| Can you describe you environment? You say you are executing a query on a SQL server, yes? How are you linking to the Access file? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-05 : 02:45:52
|
| "I would prefer passing dates in CCYY-MM-DD HH:MI:SS format"Note that you have to leave the "-" out, or put a "T" in place of the space for unambiguous implicit conversion'CCYYMMDD''CCYYMMDD HH:MI:SS''CCYY-MM-DDTHH:MI:SS' |
|
|
|
|
Select ... Where Date > 12/31/2005