SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 get rows created after a certain time
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

badpig521
Starting Member

USA
14 Posts

Posted - 02/11/2009 :  08:37:32  Show Profile  Reply with Quote
I have this query:

SELECT  
		[SourceTable] = N'ActorNames',
        ACTORS.ActorID AS [ActorIDPrimaryKey],
        ACTORS.SIDNum,
        ACTORNAMES.LastName,
        ACTORNAMES.FirstName,
        ACTORNAMES.MiddleName,
        ACTORNAMES.Suffix,
        ACTORNAMES.Salutation,
        ACTORNAMES.OrganizationName,
        CONVERT(VARCHAR(8), ACTORNAMES.an_CreateDate, 112) AS create_date
FROM    ACTORS
        INNER JOIN ACTORNAMES ON ACTORS.ActorID = ACTORNAMES.ActorID
WHERE   ( NOT ( ACTORNAMES.an_Creator = 'cjis' )
        )
ORDER BY ACTORS.ActorID


Lets say I run this today @ 7 am and get all the results. Now, I want to run this query and get results that were created after 7AM on this day using the an_createdate column. How would you write it?

SwePeso
Patron Saint of Lost Yaks

Sweden
30218 Posts

Posted - 02/11/2009 :  08:44:12  Show Profile  Visit SwePeso's Homepage  Reply with Quote
WHERE   ( NOT ( ACTORNAMES.an_Creator = 'cjis' )
        )
	AND an_createdate >= '20090211 07:00:00'



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/11/2009 :  08:49:50  Show Profile  Reply with Quote
an_createdate >= DATEADD(hh,7,DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0))
Go to Top of Page

badpig521
Starting Member

USA
14 Posts

Posted - 02/11/2009 :  08:50:32  Show Profile  Reply with Quote
quote:
Originally posted by Peso

WHERE   ( NOT ( ACTORNAMES.an_Creator = 'cjis' )
        )
	AND an_createdate >= '20090211 07:00:00'



E 12°55'05.63"
N 56°04'39.26"




ok, that makes sense, but is there a way to make it a little more automatic. Here is what I am trying to do. I am creating an SSIS package that would run 3 Xs a day after my initial extract. I am pulling the data into a flat file. I would like to run it pulling the data created on each day after a certain time. For example, on this day, I run a job @ Noon pulling data created after 7 am. Does that make sense or am I way off?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/11/2009 :  08:54:41  Show Profile  Reply with Quote
quote:
Originally posted by badpig521

quote:
Originally posted by Peso

WHERE   ( NOT ( ACTORNAMES.an_Creator = 'cjis' )
        )
	AND an_createdate >= '20090211 07:00:00'



E 12°55'05.63"
N 56°04'39.26"




ok, that makes sense, but is there a way to make it a little more automatic. Here is what I am trying to do. I am creating an SSIS package that would run 3 Xs a day after my initial extract. I am pulling the data into a flat file. I would like to run it pulling the data created on each day after a certain time. For example, on this day, I run a job @ Noon pulling data created after 7 am. Does that make sense or am I way off?


you can make source query like this

...
WHERE...
and an_createdate >= DATEADD(hh,7,DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0))

this will ensure it always take current days records from 7 am onwards
Go to Top of Page

badpig521
Starting Member

USA
14 Posts

Posted - 02/11/2009 :  08:56:11  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

quote:
Originally posted by badpig521

quote:
Originally posted by Peso

WHERE   ( NOT ( ACTORNAMES.an_Creator = 'cjis' )
        )
	AND an_createdate >= '20090211 07:00:00'



E 12°55'05.63"
N 56°04'39.26"




ok, that makes sense, but is there a way to make it a little more automatic. Here is what I am trying to do. I am creating an SSIS package that would run 3 Xs a day after my initial extract. I am pulling the data into a flat file. I would like to run it pulling the data created on each day after a certain time. For example, on this day, I run a job @ Noon pulling data created after 7 am. Does that make sense or am I way off?


you can make source query like this

...
WHERE...
and an_createdate >= DATEADD(hh,7,DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0))

this will ensure it always take current days records from 7 am onwards



Perfect, thats what I was looking for! Thank you all!!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/11/2009 :  09:00:02  Show Profile  Reply with Quote
welcome
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000