How to "Date" help

Question

Post reply

How to "Date" help

chaddresnick

Grasshopper

Points: 11
More actions
February 15, 2016 at 2:26 pm

#311435

I am looking to write a SQL query and use a dynamic date. I want the SQL to ALWAYS use 1/1 to 12/31 of the prior calendar year. So, if it is run at any point in 2016, it will return all results from 1/1/15 to 12/31/15. If I run it in 2017, the dates will automatically change to 1/1/16 to 12/31/2016. Is this possible?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jacob Wilkins One Orange Chip Points: 27972 More actions · Answer 1

Something like this?

CREATE TABLE #test (ID int, some_date datetime);

INSERT INTO #test (ID,some_date) VALUES

(1,'20150607'),

(2,'20140609'),

(3,'20160203');

SELECT *

FROM #test

WHERE some_date>=DATEADD(yy,DATEDIFF(yy,0,GETDATE())-1,0)

AND

some_date<DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0);

DROP TABLE #test;

Cheers!

EDIT: Horror of horrors, I noticed one of my statements was not terminated with a semicolon 🙂

Ed Wagner SSC Guru Points: 287015 More actions · Answer 2

If you're looking for a good reference for many common date functions similar to your requirement, Lynn Pettis has a great post at http://qa.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 3

select datefromparts(year(getdate())-1,1,1) as beginning_of_last_year,

datefromparts(year(getdate())-1,12,31) as end_of_last_year;

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 4

Orlando Colamatteo (2/16/2016)
select datefromparts(year(getdate())-1,1,1) as beginning_of_last_year,
datefromparts(year(getdate())-1,12,31) as end_of_last_year;

I don't like using closed ended ranges for date ranges. Prefer close ended on the lower end and open ended on the upper end of the date range. That way if the date/time data type changes you don't have to worry about changing the range criteria.

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 5

Lynn Pettis (2/16/2016)
Orlando Colamatteo (2/16/2016)
select datefromparts(year(getdate())-1,1,1) as beginning_of_last_year,
datefromparts(year(getdate())-1,12,31) as end_of_last_year;
I don't like using closed ended ranges for date ranges. Prefer close ended on the lower end and open ended on the upper end of the date range. That way if the date/time data type changes you don't have to worry about changing the range criteria.

It depends on the business requirement, whether it's inclusive or exclusive.

I was only offering an alternate technique to get a date from its parts.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

webtomte Ten Centuries Points: 1125 More actions · Answer 6

Or you can do like this.

CREATE TABLE #test (ID int, some_date datetime);

INSERT INTO #test (ID,some_date) VALUES

(1,'20150607'),

(2,'20140609'),

(3,'20160203');

declare

@start_date datetime

,@end_date datetime

set @start_date = convert(nvarchar(4),dateadd(year,-1,getdate()),121) + '-01-01'

set @end_date = convert(nvarchar(4),dateadd(year,-1,getdate()),121) + '-12-31'

select

*

from

#test

where

some_date >= @start_date

AND

some_date < @end_date

DROP TABLE #test;