Better way of finding date?

Question

Better way of finding date?

thomashohner

SSCrazy Eights

Points: 9856
More actions
October 26, 2015 at 9:53 am

#321722

Hello All,
I have some code that works for my situation. However it is kind of clunky. Is there a better method of doing this?
Here is the requirement.
November 6 of the year prior to the Report year
Here is my current code which gives the correct answer just looking for a more simplified or elegant solution.
DECLARE @StartDate DATE = '01/01/15'
SELECT '11/06/' + CAST(DATEPART(YEAR,DATEADD(YEAR, -1, @StartDate)) AS VARCHAR (10))
Thanks!
***SQL born on date Spring 2013:-)

Viewing 10 posts - 1 through 9 (of 9 total)

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

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

Just to be clear: if @StartDate is November 28th, 2015 - you still want 11/6/2014? I think that's the case, just double-checking...

This is a little cleaner:

DECLARE @StartDate DATE = '01/01/15';

SELECT '11/06/' + CAST(YEAR(@StartDate)-1 AS char(4));

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

thomashohner SSCrazy Eights Points: 9856 More actions · Answer 2

Yes sir that is correct. Wow, I love it I knew their was a better way!

Thanks Alan!!

***SQL born on date Spring 2013:-)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Alan.B (10/26/2015)
Just to be clear: if @StartDate is November 28th, 2015 - you still want 11/6/2014? I think that's the case, just double-checking...
This is a little cleaner:
DECLARE @StartDate DATE = '01/01/15';
SELECT '11/06/' + CAST(YEAR(@StartDate)-1 AS char(4));

Is this ddmmyyyy or mmddyyyy? I know that 'yyyymmdd' is unambiguously cast to a date(time) but this format may be sensitive to regional settings.

SELECT CAST('06/31/2015' AS datetime)

-- Msg 241, Level 16, State 1, Line 1

Conversion failed when converting date and/or time from character string.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

thomashohner SSCrazy Eights Points: 9856 More actions · Answer 4

Hi Allan,

The format in the date column I am filtering on is like this YYYYMMDD

Your code seems to be working perfectly though in my report query.

Thanks!

***SQL born on date Spring 2013:-)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

Here's a different approach with 2 variants:

DECLARE @StartDate DATE = '01/01/15';

SELECT DATEADD( YY, DATEDIFF( YY, 0, @StartDate), '18991106'),

DATEADD( YY, DATEDIFF( YY, 0, @StartDate), -56),

CAST( CAST( '18991106' AS datetime) AS int)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 6

Don't think DATEFROMPARTS() was available in SQL 2008? but for later versions I would use

DECLARE @StartDate DATE = '01/01/15';

SELECT DATEFROMPARTS(YEAR(@StartDate)-1, 11, 6);

which avoids all the ambiguity of "nn/nn/nn" style string dates which parse differently depending on the settings of the currently logged on user.

SET LANGUAGE 'JAPANESE'

will parse '01/01/2015' as expect but '01/01/15' as 2001-01-15 !

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Does this work (can't test it, on a flight to Seattle)?

declare @startdate datetime = "20151126";

select dateadd(day,5,dateadd(month,10,dateadd(year,year(@startdate) - 1900 - 1),0)));

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

Lynn Pettis (10/26/2015)
Does this work (can't test it, on a flight to Seattle)?

declare @startdate datetime = "20151126";
select dateadd(day,5,dateadd ( month,10,dateadd ( year,year(@startdate) - 1900 - 1),0)));

Corrected version for Lynn's code:

declare @startdate datetime = '20151126';

select dateadd(day,5,dateadd( month,10,dateadd( year,year(@startdate) - 1900 - 1,0)));

Enjoy your time in Seattle. 😉

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 9

Just another country heard from... this one returns a DATETIME datatype and works in all versions of SQL Server.

DECLARE @StartDate DATE = '01/01/2015';

SELECT DATEADD(yy,YEAR(@StartDate)-1901,'19001106');

I still like Luis' method better though. Here's his entry again...

DECLARE @StartDate DATE = '01/01/2015';

SELECT DATEADD( YY, DATEDIFF( YY, 0, @StartDate), '18991106')

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)