Possible to determine the datetime range of loaded data to staging table?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 2005 » SQL Server 2005 Integration Services » Possible to determine the datetime range of...

Add to briefcase

18 posts, Page 2 of 2

««1 2

Possible to determine the datetime range of loaded data to staging table?

Expand / Collapse

Author

Message

Posted Thursday, December 27, 2012 10:33 PM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:56 PM
Points: 479, Visits: 405

You may write the expression something like this in the conditional split

(DT_DBTIMESTAMP)(event_d + " " + event_t) >= DATEADD("HH",-73,GETDATE()) && (DT_DBTIMESTAMP)(event_d + " " + event_t) <= DATEADD("HH",8,(DT_DBDATE)(GETDATE()))

Post #1400792

Posted Friday, December 28, 2012 12:33 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 5:12 AM
Points: 4,226, Visits: 9,458

Here's my pseudo-code expanded a bit to accommodate the start and end date bits:

declare @StartDate datetime
	,@EndDate datetime

set @EndDate = dateadd(hour, 8, DATEADD(dd, 0, DATEDIFF(dd, 0, DATEADD(HOUR, - 8, getdate()))))
set @StartDate = dateadd(hour, - 73, @EndDate)

select col1
	,col2
from table
where [convert varchar date and time to datetime] between @StartDate
		and @EndDate

That's how it works in T-SQL. Just need to convert that to Oracle.

--Edit: fixed typo.

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1400808

Posted Friday, December 28, 2012 1:40 AM

SSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 02, 2013 11:09 AM
Points: 97, Visits: 180

Thanks to both of you, currently out of office cant test our both solutions. Will test as soon as i get back. Btw from what i read from both your solutions, i wonder if you all took care of scenario whereby package i executed before or at 8am. Which means this examples:

Datetime when package run: 26/12/12 0759
Startdate: 22/12/12 0700
Enddate: 25/12/12 0800

Datetime when package run: 26/12/12 0800
Startdate: 22/12/12 0700
Enddate: 25/12/12 0800

Just clarifying. Thanks alot

Post #1400826

Posted Friday, December 28, 2012 2:04 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 5:12 AM
Points: 4,226, Visits: 9,458

i wonder if you all took care of scenario whereby package i executed before or at 8am.

My solution handles that.

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1400831

Posted Friday, December 28, 2012 2:24 AM

SSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 02, 2013 11:09 AM
Points: 97, Visits: 180

Phil Parkin (12/28/2012)

i wonder if you all took care of scenario whereby package i executed before or at 8am.

My solution handles that.

Thanks!

BigGrin

Post #1400841

Posted Friday, December 28, 2012 8:21 AM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:56 PM
Points: 479, Visits: 405

My solution handles too

Post #1400934

Posted Wednesday, January 02, 2013 12:47 AM

SSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 02, 2013 11:09 AM
Points: 97, Visits: 180

SatishAyyar (12/28/2012)

My solution handles too

Hi satish,

I tried out your solution, it does not met my requirement. I guess your solution take 73 hrs before my current sys date time. This is not what i want. The start time should be 73 hours ago from the end time while the end time is the nearest 8am(past). Thanks anyway. Btw if u are not sure what i am talking about, kindly study the scenarios. Thanks for the input

Post #1401682

Posted Wednesday, January 02, 2013 12:49 AM

SSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 02, 2013 11:09 AM
Points: 97, Visits: 180

Phil Parkin (12/28/2012)

i wonder if you all took care of scenario whereby package i executed before or at 8am.

My solution handles that.

From what i see, phil your solution will definitely work. However i prefer doing it in conditional split for this. Do not how to convert ur tsql into conditional split expression

Post #1401683

« Prev Topic | Next Topic »

Add to briefcase

18 posts, Page 2 of 2

««1 2

Permissions

Expand / Collapse