Match year part of a date
Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Classic ASP Discussion
Forum Description: Discussion on Active Server Pages (Classic ASP).
URL: https://forums.webwiz.net/forum_posts.asp?TID=1414
Printed Date: 29 March 2026 at 10:26am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com
Topic: Match year part of a date
Posted By: pedalcars
Subject: Match year part of a date
Date Posted: 27 March 2003 at 3:13pm
|
Following on from an earlier enquiry, is it possible to match part of a date field in an SQL query?
EG: If I have lngYear = 2003, can I have: strSQL = SELECT tblTable.* WHERE (the year part of)tblTable.Date = " & lngYear & ";" ?? Failing that, it wouldn't be much trouble for me to use (eg) 1/1/2003 (instead of simply 2003). I would then need to SELECT WHERE tblTable.Date was between 1/1/2003 and 1/1/2003 plus one year, which is something else I don't know how to do! Ideally I'd like to use the first option though, if possible. Ta ------------- http://www.pedalcars.info/ - www.pedalcars.info The most fun on four wheels 
|
Replies:
Posted By: Gary
Date Posted: 28 March 2003 at 2:20am
|
strSQL = SELECT tblTable.* WHERE (YEAR)tblTable.Date = " & lngYear & ";" |
Posted By: MorningZ
Date Posted: 28 March 2003 at 5:22am
|
actually
strSQL = "SELECT * WHERE SELECT DATEPART( year, [Date] ) = " & lngYear Also note, its baaaaaaaaaaaaaaaaad practice to use reserved words as column names, you are just asking for trouble.... in the above example, there's [ ]'s to combat this, but its still bad practice ------------- Contribute to the working anarchy we fondly call the Internet |
Posted By: pedalcars
Date Posted: 01 April 2003 at 4:48am
|
Thanks MorningZ, that would explain why it wasn't working to start with!
As it happens the column isn't actually called "Date" (it's "Start_Date" - is that OK?); I just abbreviated for simplicity of typing. The table isn't called tblTable, either! ------------- http://www.pedalcars.info/ - www.pedalcars.info The most fun on four wheels
|
Posted By: pedalcars
Date Posted: 01 April 2003 at 5:10am
|
Hmm. Still got a problem.
Here's the code (not the full statement I want to use, but an initial, simple one, to test the theory): strSQL = "SELECT tblDates.Start_Date FROM tblDates WHERE SELECT DATEPART(year,tblDates.Start_Date) = " & lngYear & ";" And here's the error... Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC Microsoft Access Driver] Syntax error. in query expression 'SELECT DATEPART(year,tblDates.Start_Date) = 2003'. I'm guessing the syntax bit is the second use of "SELECT". Taking it out gives, instead: Microsoft OLE DB Provider for ODBC Drivers error '80040e10' [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. lngYear exists (I've response.writen the strSQL and it shows as strSQL = SELECT tblDates.Start_Date FROM tblDates WHERE DATEPART(year,tblDates.Start_Date) = 2003; Any more ideas? Ta ------------- http://www.pedalcars.info/ - www.pedalcars.info The most fun on four wheels
|
Posted By: MorningZ
Date Posted: 01 April 2003 at 5:17am
|
well yeah, the first error told you that "SELECT" didn't belong... (my bad on the typo, unfortunately there's no "validate SQL/Script" on this forum
as for the second error, i tried the following and it worked perfect SELECT * FROM Trltbl WHERE DatePart( year, SignDate ) = 2003 ------------- Contribute to the working anarchy we fondly call the Internet |
Posted By: pedalcars
Date Posted: 01 April 2003 at 5:32am
|
No, I don't understand why it's not working either! I have replace the lngYear bit with a number as per your example (tried 2002 and 2003 which are both present in strat_Date) and it's still throwing a wobbly.
Going to take a break, restart, come back, kick it, and try agin... ------------- http://www.pedalcars.info/ - www.pedalcars.info The most fun on four wheels
|
Posted By: michael
Date Posted: 01 April 2003 at 8:16am
|
Well if you are using access this would not work, actually much easier you could do: SELECT * FROM Trltbl WHERE year(SignDate) = 2003 ------------- http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker |
Posted By: pedalcars
Date Posted: 01 April 2003 at 9:30am
|
And Michael wins the beer this time! Thanks all.
Just leaves one other problem but I'll post that seperately. Edit (additional): No I wont. Six hours off, a much nicer work space and some brain-wash TV later, I've sorted the last major obstacle that I think I have. Hooray! ------------- http://www.pedalcars.info/ - www.pedalcars.info The most fun on four wheels
|