declaration

• mtz676

  SSCrazy Eights

  Points: 9261

  More actions

  February 2, 2015 at 5:21 am

  #314900

  @CDATE=current date - 2 and time (00:00)

  @DDATE=current date - 2 and time (23:59:59)

  If today is 3rd then should be

  CDATE should be 2 days before where time equals 00:00

  and DDATE should be 2 days before where time equals 23:59:59

  How do I declare the above parameters in a t-sql Stored procedure which will be run inside an SSIS package

• Lowell

  SSC Guru

  Points: 323546

  More actions

  February 2, 2015 at 5:32 am

  #1774746

  the dateadd and datediff functions together, are the best ways to do this.

  so you might now that getdate() is the right now timestamp, all the way down to milliseconds.

  by using a combination of functions, you can trim off and get midnight of the current date:

  select DATEADD(dd, DATEDIFF(dd,0,getdate()), 0) --2015-02-02 00:00:00.000

  so your start date, which is midnight two days prior just needs an extra minus two days.

  select DATEADD(dd,-2,DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)) --2015-02-01 00:00:00.000

  and the other date, which is a bit more than one day, you do something similar with seconds or even milliseonds::

  select dateadd(ms,-3,DATEADD(dd,-1,DATEADD(dd, DATEDIFF(dd,0,getdate()), 0))) --2015-01-31 23:59:59.997

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• ScottPletcher

  SSC Guru

  Points: 101200

  More actions

  February 2, 2015 at 3:32 pm

  #1774896

  Rather than use a specific ending value, most people prefer to use < the next day. That method always works, even if the date/datetime format changes later, something like this:

  SET @CDATE = DATEADD(DAY, -2, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))

  SET @DDATE = DATEADD(DAY, -1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))

  WHERE

  {date_column} >= @CDATE AND

  {date_column} < @DDATE --NOTE: < not <=

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".