SQL Date Query

Hi  and thanks for looking at my question.

I would like to query a date field and bring back all records for the current year.

Any ideas?


Who is Participating?
try this
select * from tablename where datepart(year,datecolumn)=datepart(year,getdate())

Open in new window

Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do:
select * from yourtable
where your_date_field >= CONVERT(datetime, CONVERT(varchar(4), getdate(), 120) + '-01-01', 120)
  and your_date_field >= dateadd(year, 1, CONVERT(datetime, CONVERT(varchar(4), getdate(), 120) + '-01-01', 120))

Open in new window

check this solution :

select * from my_table where datepart(year, my_date_field) = datepart(year, getdate())
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

PigdogmonsterAuthor Commented:
Thanks -  thats great.

I am just trying to put this in my ASP code.

sqltext23="SELECT SUM(Tot_Monthly_Miles) AS 'tot_monthly',SUM(Tot_Business_Miles) AS 'tot_business' from Finncos_Data where user_ref='"&varUser_Ref&"' "

Can you help add to this query as it errors.

A little simpler:

SELECT * FROM yourtable
WHERE DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) = DATEADD(yy, DATEDIFF(yy,0,your_date_field), 0)

Open in new window

PigdogmonsterAuthor Commented:
Great!  I encorporated it like this..sqltext23="SELECT SUM(Tot_Monthly_Miles) AS 'tot_monthly',SUM(Tot_Business_Miles) AS 'tot_business' from Finncos_Data where user_ref='"&varUser_Ref&"' AND datepart(year,Date_Added)=datepart(year,getdate())"
Guy Hengel [angelIII / a3]Billing EngineerCommented:
* using  where datepart(year, my_date_field) = datepart(year, getdate()) will make sure an index on the my_date_field cannot be used.  the suggestion given by openshac is of the same results...

  my suggestion can use a index.

* you accepted only the second comment giving that code, bokist posted the same idea 1 minute earler.
   at least a split of the points would have been fair
PigdogmonsterAuthor Commented:
Thank you Gentlemen for all your comments.

Apologises for not sharing the points correctly - I opened the link from Chandan_Gowda's comment first.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.