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.
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_dateFROM ACTORS INNER JOIN ACTORNAMES ON ACTORS.ActorID = ACTORNAMES.ActorIDWHERE ( 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" |
|
|
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] |
|
|
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? |
|
|
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 |
|
|
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!! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-11 : 09:00:02
|
welcome |
|
|
|
|
|
|
|