Solved

Date Diff in SQL

Posted on 2016-07-20
6
67 Views
Last Modified: 2016-07-21
In access would add this to my criteria: > CDate("1/1/" & Year(Now())-1  to get anything greater than 1/1/YYYY.  I need to calculate YTD metrics as well as PY YTD metrics.  

YTD:  1/1/2016-present
PYTD: 1/1/2015-12/31/2015

How can I translate this to SQL?  I need to automate this.
0
Comment
Question by:gracie1972
  • 3
  • 2
6 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41721922
Year - CAST(CAST(Year(SomeDate) as char(4)) + '0101' as date)

What's the logic for PYTD?  That can wildly differ from place to place, so check out my article SQL Server Calendar Table which gives you code and a demo on how to pull this off to handle custom periods.
0
 

Author Comment

by:gracie1972
ID: 41721927
For Prior Year I have to include all the dates greater than 1/1/2015.  (Current year - 1).

In access this worked:  > CDate("1/1/" & Year(Now())-1
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41721934
datediff (year,1,getdate ())
That gives you the number of years from 1901 until this year

Now add that to "year zero" (1900)

dateadd (year, datediff (year,1,getdate ()) ,0)

Which will return a datetime value of:
Jan 1 last year
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:gracie1972
ID: 41721948
This only returned greater than 1/1/2016, I need for this query to run and by default always go back 1 year, YTD starting at the beginning of the year.  

When I write the where statement like this, I only get > 1/1/2016.
WHERE dbo.VW_Tableau_Sell_Out.[WEEK] > dateadd (year, datediff (year,1,getdate ()) ,0)

If today were 7/19/2017, then the query should return > 1/1/2016, we are trying to automate this. Access cannot work any longer the data pulls are too large.
This worked in Access: WHERE (dbo_VW_Tableau_Sell_Out.WEEK)>CDate("1/1/" & Year(Now())-1)
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 41722164
oops, sorry

===
select  dateadd (year, datediff (year,0,getdate ())-1 ,0)

0 in the formula above represents 1900-01-01

get the datediff, in years, from that 0 date until getdate()
then deduct 1
then add that number of years to the zero date

e.g.  if getdate() is in the year 2016, the returned date would be 2015-01-01

=== e.g. === to get all data for the previous year
select *
from some_table
where datetime_col >= dateadd (year, datediff (year,0,getdate ())-1 ,0) -- 1st Jan prev year
and datetime_col <  dateadd (year, datediff (year,0,getdate ()) ,0) -- 1st Jan this year
0
 

Author Closing Comment

by:gracie1972
ID: 41723377
Thank you!
0

Featured Post

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

920 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

14 Experts available now in Live!

Get 1:1 Help Now