Developer Forum

First, don't use awful formats like 8/10/1009. On some systems, that will
be interpreted as August 10th, on others October 8th. I strongly suggest
always using YYYYMMDD for date-only literals in SQL Server.

Second, don't rely on offsets like 364 days. On leap years, your formula
will suddenly break, and while you may not miss a lot of data on New Years'
Eve depending on your business, it will still look funny in reports.

Probably the easiest way is:


DECLARE
@DateValue SMALLDATETIME,
@BeginningOfYear SMALLDATETIME,
@EndOfYear SMALLDATETIME;

SELECT
@DateValue = '20090810',
@FirstDayOfRange = DATEADD(YEAR, DATEDIFF(YEAR, '19000101', @DateValue),
'19000101'),
@LastDayOfRange = DATEADD(DAY, -1, DATEADD(YEAR, 1, @FirstDayOfRange));

SELECT
@DateValue,
@BeginningOfYear,
@EndOfYear;


However, if you are going to use these dates in a BETWEEN clause, unless you
are certain that your data will never contain a time component, much safer
to say:

AND < @FirstDayOfNextYear




On 8/10/09 6:31 PM, in article XXXX@XXXXX.COM , "Derek

I agree except the @LastDayOfRange I would calculate as follows:

SET @LastDayOfRange = DATEADD(YEAR, 1, @FirstDayOfRange) -1;

not a huge deal, but simpler in my mind.

--
-------------------------------
Jeremy Holovacs
MCITP, MCDBA, MCSE

> SET @LastDayOfRange = DATEADD(YEAR, 1, @FirstDayOfRange) -1;

I used to use this "shortcut" and it is certainly quicker to write, but do
those benefits really pay off? For example, have you tried it with the new
DATE data types in SQL Server 2008?


DECLARE @d DATE;
SELECT @d = GETUTCDATE();
SELECT @d = @d + 1;

Msg 206, Level 16, State 2, Line 3
Operand type clash: date is incompatible with int


As a result, I've been very careful to avoid shortcuts that might compromise
the future-proofness of my code.

A

So what is the best way to get @FirstDayOfNextYear ?

> So what is the best way to get @FirstDayOfNextYear ?


I thought I already kind of covered that (just don't subtract a day), but
let's do it again.

DECLARE
@FirstDayOfThisYear SMALLDATETIME,
@FirstDayOfNextYear SMALLDATETIME;

SELECT
@FirstDayOfThisYear = DATEADD(YEAR, DATEDIFF(YEAR, '19000101',
CURRENT_TIMESTAMP), '19000101'),
@FirstDayOfNextYear = DATEADD(YEAR, 1, @FirstDayOfThisYear);

SELECT
@FirstDayOfThisYear, @FirstDayOfNextYear;

The best way to handle dates in general is with a calendar table; the
data ports, can handle all the holidays, changes, etc. and it is
faster than re-computing things over and over.

And nobody wants to write the code for Easter (was that Roman Catholic
Easter or Orthodox Easter?).

Thank you. One more question, very much along the same lines.

I need to figure out a fiscal date. I will have the month and year passed
in. For the month, I will have a number such as 7 for July, and the year
will be passed in as a four digit integer.

So I have 7 and 2009, and I need to get 7/31/2009. I know I can use a month
calculation and parse this all together as text, but is there a more
efficient way?

DECLARE @m INT, @y INT, @dt SMALLDATETIME;

SELECT @m = 7, @y = 2009, @dt = RTRIM(@y) + '0101';

SELECT DATEADD(DAY, -1, DATEADD(MONTH, @m, @dt));

But again, why do you need the *last* day of the month? If you are doing
range queries, you should be thinking in terms of "greater than the first
day of this month, and less than the first day of the next month." I also
recommend you stay away from ambiguous formats like m/d/y. It's obvious
when the only date you see is 7/31/2009, however if you say 6/1/2009, the
interpretation will depend on your audience. In SQL Server I strongly
recommend always using date-only literals in the form YYYYMMDD.



On 8/11/09 1:59 PM, in article XXXX@XXXXX.COM , "Derek

Okay I will try to change to use only the yyyymmdd format. I forgot to ask
one other point. If I have the 7/31/2009, I need to get the full fiscal
year. So then to finally get the full range, how to I get the date 8/1/2008
from this? So the range I will check is 8/1/2008 - 7/31/2009. But as you
stated I may not want the solution below to get the range. So the range
statement in sql will be:

But I do also need @LastDayOfFiscal because this needs to be stored in the
database.

Can you do a final tweak on this to show all three?

ay have answered my question. Would you tell me if you see any
inefficiencies in writing the procedure this way?
Set @DateValue = '20090512'
Set @LastMonth = 7
Set @Year = Year(@DateValue)
Set @dt = RTRIM(@Year) + '0101'
Set @FirstDayOfRange = dateadd(day, -1, dateadd(month, @LastMonth, @dt))
Set @LastDayOfRange = dateadd(day, 0, dateadd(month, @LastMonth-12, @dt))

So the dates I get are 8/1/2008 - 7/31/2009, which is the fiscal year.
Correct data, but any flaws in this process?

"Derek Hart" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...