Dynamic date in a query/export

Question

Post reply

Dynamic date in a query/export

bellefso

SSC-Addicted

Points: 492
More actions
September 22, 2006 at 3:18 pm

#117005

Hi,
I need to create a DTS job that will export a months worth of data. I need to get take the current month and subtract 1 from it and insert that into the month string. I have the code to get that using the datepart function. I am probably missing something really easy but I can't seem to make it work. In this case the query will be run on like the 5th of the month and will need to return data from the first of the previous month to the last day of the month.
The where clause is something like this:
apptdate >= ts '2006-08-01 00:00:00' AND apptdate < ts '2006-09-01 00:00:00' I would like to replace the month and year on each with the previous month (and previous year for reports in Jan.) And the current month and year for the second part. Thanks, Brian

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

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

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 1

Try this:

Declare @dt_Date datetime,

@dt_EndDate datetime

set @dt_Date=getdate()

select @dt_EndDate= Cast(Convert(Char(10),DateAdd(d,-1* Datepart(d,@dt_Date) + 1,@dt_Date),101) as datetime)

Select DateAdd(m,-1,@dt_EndDate) as BeginDate,@dt_EndDate as EndDate

Thanks

Sreejith

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 2

Brian,

Here's a slightly different way that's usually faster than all of the character conversions... pretty easy on the eyes, too... DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME

SET @StartDate = DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0)

SET @EndDate = DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)

PRINT @StartDate

PRINT @EndDate

To use something similar against a table using the WHERE clause that you propose...

SELECT yourcollist

FROM yourtable

WHERE ApptDate >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0)

AND ApptDate < DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)

Nice to see someone figure out that BETWEEN doesn't cut it here and that the only way an index seek might be used is when you don't wrap ApptDate in a formula... well done, Brian.

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

bellefso SSC-Addicted Points: 492 More actions · Answer 3

Awesome. Both of these work as needed. The question that I have is how they return the date as of the 1st of each month. I looked at BOL and it didn't seem obvious to me. I am trying to get a better understanding of how the date functions work. I also want to thank both of you for the quick replies!

Thanks,

Brian

Sergiy SSC Guru Points: 110208 More actions · Answer 4

First day of the month:

SELECT DATEADD(dd, 1-DAY(@Date), @Date)

@Date must have time portion = '00:00:00.000'

If it's not use this:

SELECT DATEADD(dd, 1-DAY(@Date), DATEADD(dd, DATEDIFF(dd, 0, @Date), 0) )

_____________
Code for TallyGenerator

bellefso SSC-Addicted Points: 492 More actions · Answer 5

OK...I think I see how this is working. Correct me if I am wrong

This does: 1 - DAY(getdate()) which will return the current day, which would be 26 today. This translates into -25 being added to the current date. So whatever day you need to get should replace the '1'. If I wanted to get the fifth of every month I would write it like this: 5 - DAY(getdate()) ?

Thanks for everyone's help.

Brian

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 6

Serqiy, cool function... especially great for bi-monthly paydays on the 1st & 15th, etc.

Bellefso... this returns the first date of the current month... was in both Serqiy's and my examples...

SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)

This returns the first date of any month/year...

SELECT DATEADD(mm,DATEDIFF(mm,0,somedatehere),0)

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