Help with Year calculations

Question

Post reply

Help with Year calculations

alorenzini

SSCarpal Tunnel

Points: 4620
More actions
March 10, 2008 at 1:39 pm

#187365

I need help on calculating a years start date and end data based on a year being passed into a proc.
Passed in: 2007
In this case I would need
StartDate = 01/01/2007
EndDate = 12/31/2007
Hope that makes sense.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

Viewing 15 posts - 1 through 15 (of 28 total)

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

SELECT CAST('2007' AS DATETIME)

SELECT DATEADD(yy,1,CAST('2007' AS DATETIME))

--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)

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

Jeff, since the OP wants the end of the year wouldn't you want this:

SELECT @startdate = CAST('2007' AS DATETIME)

SELECT @enddate = DATEADD(yy,1,CAST('2007' AS DATETIME)) - 1

I know why you do it the other way, testdate >= @startdate and testdate < @enddate, which is also the better way to do date range testing.

😎

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 3

SELECT CAST('2007' AS DATETIME) gave me 2007-01-01 00:00:00.000

which is correct but

SELECT DATEADD(yy,1,CAST('2007' AS DATETIME)) gave me 2008-01-01 00:00:00.000 which is incorrect. I need 2007-12-31.

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 4

OK, make since but I am having an issue with the syntax:

DECLARE @ConsultantID VARCHAR(20)

DECLARE @StartDate datetime

DECLARE @EndDate datetime

DECLARE @Year CHAR(4)

SET @COnsultantID = '0000003'

SET @StartDate = NULL

SET @EndDate = NULL

SET @Year = 2007

IF @Year <> NULL

SET @StartDate = SELECT CAST(@Year AS DATETIME)

SET @enddate = SELECT DATEADD(yy,1,CAST(@Year AS DATETIME)) - 1

When I run this I get the following error:

Msg 156, Level 15, State 1, Line 12

Incorrect syntax near the keyword 'SELECT'.

Msg 156, Level 15, State 1, Line 13

Incorrect syntax near the keyword 'SELECT'.

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

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

DECLARE @ConsultantID VARCHAR(20)

DECLARE @StartDate datetime

DECLARE @EndDate datetime

DECLARE @Year CHAR(4)

SET @ConsultantID = '0000003'

SET @StartDate = NULL

SET @EndDate = NULL

SET @Year = 2007

-- IF @Year <> NULL

-- SET @StartDate = SELECT CAST(@Year AS DATETIME)

-- SET @EndDate = SELECT DATEADD(yy,1,CAST(@Year AS DATETIME)) - 1

IF @Year is not NULL

begin

set @StartDate = cast(@Year as datetime)

set @enddate = dateadd(yy, 1, cast(@Year as datetime)) - 1

end

Try that...

😎

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 6

That worked fine. Thanks.

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

Lynn Pettis (3/10/2008)
Jeff, since the OP wants the end of the year wouldn't you want this:
SELECT @startdate = CAST('2007' AS DATETIME)
SELECT @enddate = DATEADD(yy,1,CAST('2007' AS DATETIME)) - 1
I know why you do it the other way, testdate >= @startdate and testdate < @enddate, which is also the better way to do date range testing.
😎

No... the Op is wrong... the OP isn't considering the fact that the dates could have times and the method you used will ignore the whole last day except for perfect midnight times. Code should be...

SELECT @StartDate = CAST('2007' AS DATETIME),

@EndDate = DATEADD(yy,1,CAST('2007' AS DATETIME))

SELECT yada-yada

FROM sometable

WHERE somedatecolumn >= @StartDate

AND someDateColumn < @EndDate

--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)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

alorenzini (3/10/2008)
That worked fine. Thanks.

See above and reconsider how much hell you will go through if any of the dates ever come in with a time other than midnight... and don't say it can't happen... it will happen.

--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)

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

Jeff Moden (3/10/2008)
Lynn Pettis (3/10/2008)
Jeff, since the OP wants the end of the year wouldn't you want this:
SELECT @startdate = CAST('2007' AS DATETIME)
SELECT @enddate = DATEADD(yy,1,CAST('2007' AS DATETIME)) - 1
I know why you do it the other way, testdate >= @startdate and testdate < @enddate, which is also the better way to do date range testing.
😎
No... the Op is wrong... the OP isn't considering the fact that the dates could have times and the method you used will ignore the whole last day except for perfect midnight times. Code should be...
SELECT @StartDate = CAST('2007' AS DATETIME),
@EndDate = DATEADD(yy,1,CAST('2007' AS DATETIME))
SELECT yada-yada
FROM sometable
WHERE somedatecolumn >= @StartDate
AND someDateColumn < @EndDate

Jeff,

I agree with you. If you reread my comments you'll see that I used the same logic in my comment as you did in your code snippet. Sometimes, however, we need to give the OP what they asked for and then show them the light in the hopes that they see it.

Of course when we go to SQL 2008 and use the DATE type, we won't have to worry about the time portion.

😎

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 10

Sorry Lynn... it was more directed at the OP than you...

Also, we'll probably disagree on this, but the new DATE only and TIME only datatypes are a real loss of important data so far as I'm concerned. I won't ever allow my Developers to either one of them.

--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)

Jack Corbett SSC Guru Points: 184394 More actions · Answer 11

I like to use between when doing date ranges, I just think it is easier to read. I would use DateAdd(SS, -1, DateAdd(YY, 1, Convert(datetime, @year))) to set my end date and then the between would be fine, I think.

Also I do see value in the DATE datatype and would use it. For example in a personnel/HR app for birth date you really don't care about the time portion so using just the date makes since instead of storing the date plus midnight. Like just about anything else we need to be careful to use the correct datatype and not fall in love with the new features. That being said I always hated working with legacy apps that had date and time columns when they really wanted a datetime column.:P

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 12

Jack Corbett (3/10/2008)
I like to use between when doing date ranges, I just think it is easier to read. I would use DateAdd(SS, -1, DateAdd(YY, 1, Convert(datetime, @year))) to set my end date and then the between would be fine, I think.

So... what happens to the transactions that occur at 23:59:59.003 through 23:59:59.997? Yeah... I know... someone is going to say "Well shoot... I use DateAdd(ms,-3,DateAdd(yy,1)Convert(DateTime,@Year))))... what'cha gonna do with the new date type that goes down to milliseconds and you get someone in trouble with taxes because you actually include something in the wrong year. Ever hear of "Tax Liability" or "Lawsuit?

Never use BETWEEN on date ranges even though it does "look nicer". Always make the enddate the beginning of the "next period" and use "<"... if you can.

--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)

Jack Corbett SSC Guru Points: 184394 More actions · Answer 13

Of course the OP doesn't mention if he is using datetime or smalldatetime. So if smalldatetime the seconds are fine. Most of the apps I have worked with are using smalldattime and the between is fine. I do understand what you are saying though. If I were to use >= and < then I would be sure to use () and ask that any other developers do the same. Just my preference for readability.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

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

I have to agree with Jack. Use the correct data type for the correct data element. I agree, unless you really need to record the time of birth, such as for birth records; yes, a datetime data type is correct. If you are recording the date of birth in an HR system, then just a date data type would be appropriate.

In the case of birth records, just so we know, I would not use seperate fields for date and time. Using seperate date time data types in this instance would not be appropriate. In fact, at this moment, I am having a difficult time coming up with a use for the time data type in an OLTP application. I can, however, see using it in a time dimension in an OLAP application.

😎