Pickink up todays date in a SQL DTS Package

[SirGally]

Hi there,

I need to automate a DTS package to run daily. I have the code Im going to use, only, it is designed for input of a 'start date' and 'end date' by a user. I need the DTS package to pick up todays date and yesterdays date automatically, rather than have the user input these dates. That way I can automate the package to run daily and not rely on the user to enter the 'start date' and 'end date' to indicate the period for which we are going to export the data.

Here is the starting code I have at the moment in the 'Transform Data Task Properties' of the link between Source Server and Destination Text file.



declare @gamingDayStart datetime
declare @gamingDayEnd datetime
exec get_bus_datetime 'Export EOD Meters', 'Bus Day Start:', @gamingDayStart output
exec get_bus_datetime 'Export EOD Meters', 'Bus Day End:', @gamingDayEnd output

set @gamingDayStart = CAST(DATEPART(mm,@gamingDayStart) as varchar(2)) + '/' + CAST(DATEPART(dd,@gamingDayStart) as varchar(2)) + '/' + CAST(DATEPART(yyyy,@gamingDayStart) as varchar(4))
set @gamingDayEnd = CAST(DATEPART(mm,@gamingDayEnd) as varchar(2)) + '/' + CAST(DATEPART(dd,@gamingDayEnd) as varchar(2)) + '/' + CAST(DATEPART(yyyy,@gamingDayEnd) as varchar(4))



I hope this all makes sense!

Thanks in advance

SirGally

 

[TomKane]

Hi,

You can use GETDATE() to get the system date and then you can use DATEADD to get yesterday's or tomorrow's dates. Do you have to take weekend days into account?

Regards,
Tom

 

[AlexCuse]

Another thing you may want to do is strip time component from the result of getdate(). This could be done using this:

---for today's date
select dateadd(day, datediff(day, 0, getdate()), 0)

---for yesterday's date
select dateadd(day, -1, dateadd(day, datediff(day, 0, getdate()), 0))

As TomKane said, you may want to use some if/else logic if you need to account for weekends (ie on monday process friday/saturday/sunday data)

Good luck,

Alex


Ignorance of certain subjects is a great part of wisdom

 

[SirGally]

Thank you so much.

Thats been a big help.

sirgally