December 10, 2004 at 2:19 pm
I'm trying to put togather a DTS package that will output data to a file based on on the date of the transaction. For example I need all transaction that are time stamped for today to be processed to an output file at midnight. But I don't want transaction that occured before today. I'm not sure how to word the T-SQL Statement. The following gives T-SQL gives me todays output:
SELECT *
FROM msglog
WHERE (msglog_crtdt > CONVERT(DATETIME, '2004-12-10 00:00:00', 102))
How will my DTS package know only to give the prior days output?
Thanks
December 10, 2004 at 3:24 pm
getdate() gives you current date time... converting (convert) it to string and trimming off (substring) the time portion will give you todays calendar date which you can look for all >=... or you cand use dateadd to get yesterday's...
December 10, 2004 at 3:26 pm
To get all the transactions for 2004-12-10,
You could do this:
select *
from msglog
where datediff(day,msglog_crtdt,'20041210') = 0
/rockmoose
You must unlearn what You have learnt
December 10, 2004 at 4:07 pm
I can't have any hard coded dates in my query because I always need to capture tranactions from the previous day. This job will run daily to capture actvity from the prior day.
December 10, 2004 at 4:50 pm
ok do:
select *
from msglog
where datediff(day,msglog_crtdt,floor(cast(getdate()-1 as float))) = 0
for previous day ( i.e. day before getdate() )
/rockmoose
You must unlearn what You have learnt
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply