Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 get rows created after a certain time

Author  Topic 

badpig521
Starting Member

14 Posts

Posted - 2009-02-11 : 08:37:32
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

30421 Posts

Posted - 2009-02-11 : 08:44:12
[code]WHERE ( NOT ( ACTORNAMES.an_Creator = 'cjis' )
)
AND an_createdate >= '20090211 07:00:00'[/code]


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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-11 : 08:49:50
[code]an_createdate >= DATEADD(hh,7,DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0))[/code]
Go to Top of Page

badpig521
Starting Member

14 Posts

Posted - 2009-02-11 : 08:50:32
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

52326 Posts

Posted - 2009-02-11 : 08:54:41
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

14 Posts

Posted - 2009-02-11 : 08:56:11
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

52326 Posts

Posted - 2009-02-11 : 09:00:02
welcome
Go to Top of Page
   

- Advertisement -