Solved

SQL Date Query

Posted on 2009-05-05
8
226 Views
Last Modified: 2012-05-06
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?

Thanks
PDM

0
Comment
Question by:Pigdogmonster
8 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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

0
 
LVL 6

Expert Comment

by:bokist
Comment Utility
check this solution :

select * from my_table where datepart(year, my_date_field) = datepart(year, getdate())
0
 
LVL 7

Accepted Solution

by:
Chandan_Gowda earned 50 total points
Comment Utility
try this
select * from tablename where datepart(year,datecolumn)=datepart(year,getdate())

Open in new window

0
 

Author Comment

by:Pigdogmonster
Comment Utility
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.

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 6

Expert Comment

by:openshac
Comment Utility
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

0
 

Author Closing Comment

by:Pigdogmonster
Comment Utility
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())"
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
2 comments:
* 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
0
 

Author Comment

by:Pigdogmonster
Comment Utility
Thank you Gentlemen for all your comments.

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

0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now