0

In Classic ASP:

I can extract the year from a date/time field:

tester=rs.fields("datestamp")
tester=DATEPART("yyyy",tester)

But I cannot seem to figure out how to make this work in a SQL statement to bring all the records from a specific year:

Select * from table1 where DATEPART("yyyy",datestamp)='2012'

and this doesn't work either:

Select * from table1 where DATEPART("yyyy",datestamp)=2012

I've looked through a zillion examples, here and elsewhere, and can't seem to find one that'll make this work. What am I doing wrong?

haim770
  • 48,394
  • 7
  • 105
  • 133
algomeysa2
  • 1
  • What is the column type of `datestamp`? – haim770 Nov 17 '14 at 21:49
  • datestamp is a data type date/time in the access database... – algomeysa2 Nov 17 '14 at 21:57
  • well, a friend pointed out that this seems to do it: Select * from table1 WHERE year(datestamp) =2012 Though I'm still curious what the syntax would be with DATEPART – algomeysa2 Nov 18 '14 at 01:17
  • You could just use `Select * from table1 where datestamp like '%2012%'` – John Nov 18 '14 at 01:33
  • Looking at this link it appears you need hashes around your date if you use datepart http://www.techonthenet.com/access/functions/date/datepart.php. Also, don't forget you need to escape double quotes if you're using ASP/VBScript, so at a guess you would use `SQL="Select * from table1 where DATEPART(""yyyy"",#datestamp#)='2012'"` - which looks hideous and open to syntax error – John Nov 18 '14 at 01:49
  • @John only if it's a static value, you you pass a variable (like a column name) the hash is not required. I think it maybe the `[]` brackets around the column name the OP is missing `SQL="Select * from table1 where DATEPART(""yyyy"", [datestamp])=""2012"""`. – user692942 Nov 18 '14 at 09:50
  • @John OP could also use `YEAR([datestamp]) = 2012`. – user692942 Nov 18 '14 at 09:51

1 Answers1

0

The function DatePart can extract from any date some values. The best explanation that i know is here: W3School.com

And this command can be used as a part of SQL string as you want, but in this case you must considerer that the main parameter change. Sample for filter by Month for less that June: 

DATEPART(month, yourvar_withdate) <= 6

Check this explanation: W3School.com-SQL

Sure that you need use a number without quotes to eval. You can check "yy" or year (without quotes) to verify.

One more note, you must have always content on DateStamp field or receive an error.

Mastercafe
  • 91
  • 9