Getdate - Set the time 1

[Schnappa]

All

I have the following piece of code that sets the desired date ranges for a query.

dp_history.effective_dt>=dateadd(mm,-1,dateadd(dd,((datepart(dd,getdate())*-1)+1),getdate())) AND dp_history.effective_dt<= dateadd(dd,((datepart(dd,getdate())*-1)),getdate())

It seems that when the query is run, the time portion of the query is set to the time portion of the Getdate() function, thus if I run the query at 1200, I miss anything that was loaded prior to 1200 on the startdate. This also means anything loaded post 1200 on the enddate is also missed out. This adds up to some seven thousand rows of transactions - not too good.

Is there a way to force the start time to be midnight on the query date provided, and also the time to be 23:59:59 on the end date from the above query.

I hope this makes sense. Thanks in advance for any help you can provide.

Cheers

Gezza

 

[bborissov]

DECLARE @tStartDate datetime
DECLARE @tEndDate datetime
SET @tStartDate = DATEADD(mm, DATEDIFF(mm, 0, DateAdd(mm,-1,GETDATE())), 0) -- Beginning of previous month

SET @tEndDate =  DATEADD(mm, 1 + DATEDIFF(mm, 0, DateAdd(mm,-1,GETDATE())), 0) -- Beginning of current month

dp_history.effective_dt>=@tStartDate AND
dp_history.effective_dt <  @tEndDate

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Schnappa]

Thanks for that BB

I have the code at the bottom of a DTS select query, and am not sure where to place the variable declaration and set statements. The full code is below, if you could let me know where they need to be placed, I would be grateful.

SELECT

dp_history.ptid AS DPTranNumber,
dp_history.acct_no AS AccountID,
dp_history.tran_code AS TranCode,
dp_history.amt AS TranAmount,
dp_history.effective_dt AS TranDate,
ISNULL(dp_history.long_desc,dp_history.description) AS TranNarration,
ad_gb_rsm.name AS OperatorName,
ad_gb_branch.short_name AS TranBranch,
pc_origin.description AS TranOriginDesc,
ad_gb_channel.short_desc AS TranChannelDesc


FROM

(((tstUCU.dbo.dp_history dp_history LEFT OUTER JOIN tstUCU.dbo.ad_gb_rsm ad_gb_rsm ON dp_history.empl_id=ad_gb_rsm.employee_id) LEFT OUTER JOIN tstUCU.dbo.pc_origin pc_origin ON dp_history.origin_id=pc_origin.origin_id) LEFT OUTER JOIN tstUCU.dbo.ad_gb_channel ad_gb_channel ON dp_history.channel_id=ad_gb_channel.channel_id) LEFT OUTER JOIN tstUCU.dbo.ad_gb_branch ad_gb_branch ON ad_gb_rsm.branch_no=ad_gb_branch.branch_no


WHERE

dp_history.effective_dt>=dateadd(mm,-1,dateadd(dd,((datepart(dd,getdate())*-1)+1),getdate())) AND dp_history.effective_dt<= dateadd(dd,((datepart(dd,getdate())*-1)),getdate())


___________________________________________________________

Cheers

Gezza

 

[bborissov]

Till now I never made a single DTS package, so I am not sure, but you could try:

DECLARE @tStartDate datetime
DECLARE @tEndDate datetime
SET @tStartDate = DATEADD(mm, DATEDIFF(mm, 0, DateAdd(mm,-1,GETDATE())), 0) -- Beginning of previous month

SET @tEndDate =  DATEADD(mm, 1 + DATEDIFF(mm, 0, DateAdd(mm,-1,GETDATE())), 0) -- Beginning of current month

SELECT

    dp_history.ptid AS DPTranNumber,
    dp_history.acct_no AS AccountID,
    dp_history.tran_code AS TranCode,
    dp_history.amt AS TranAmount,
    dp_history.effective_dt AS TranDate,
    ISNULL(dp_history.long_desc,dp_history.description) AS TranNarration,
    ad_gb_rsm.name AS OperatorName,
    ad_gb_branch.short_name AS TranBranch,
    pc_origin.description AS TranOriginDesc,
    ad_gb_channel.short_desc AS TranChannelDesc


 FROM   

    (((tstUCU.dbo.dp_history dp_history LEFT OUTER JOIN tstUCU.dbo.ad_gb_rsm ad_gb_rsm     ON dp_history.empl_id=ad_gb_rsm.employee_id) LEFT OUTER JOIN tstUCU.dbo.pc_origin     pc_origin ON dp_history.origin_id=pc_origin.origin_id) LEFT OUTER JOIN     tstUCU.dbo.ad_gb_channel ad_gb_channel ON     dp_history.channel_id=ad_gb_channel.channel_id) LEFT OUTER JOIN     tstUCU.dbo.ad_gb_branch ad_gb_branch ON ad_gb_rsm.branch_no=ad_gb_branch.branch_no


 WHERE  dp_history.effective_dt>=@tStartDate AND
        dp_history.effective_dt <  @tEndDate

If this didn't works. I mean you can't declare variables in DTS packages (I doubt about it, but...), then do it w/o variables

WHERE  
 dp_history.effective_dt >= DATEADD(mm, DATEDIFF(mm, 0, DateAdd(mm,-1,GETDATE())), 0)
 AND dp_history.effective_dt < DATEADD(mm, 1 + DATEDIFF(mm, 0, DateAdd(mm,-1,GETDATE())), 0)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Schnappa]

Thanks again BB

I spent most of the day trying to get eother of these to work. In Analyser your w/out variables could return the correct info, but when copied into the DTS, I keep getting an error, so I am still stumped.

I also tried the variable version. It parses ok, but when you try to run it, it drops the transformation and loses the source side.

Any suggestions? Look slike another day trying to work it out.

Cheers

GV