May 26, 2010 at 3:34 am
Hi,
I m using below query to get current date rows from access database to sql server database.
SELECT ID, SiteName, DRCNum, TypeID, Point, [Time], CardNumber, FormatName
FROM TransactionLog
WHERE [TIME] >= Format(DATE (), 'dd/mm/yyyy')
But now i m scheduling ssis package to run next day 3:00 AM. So how to modify above query to get previous day rows.
TIME column has values datetime ex: 25/05/2010 10:11:00 AM
Sachin
May 26, 2010 at 4:08 am
Don't understand how what you've currently got works.
But this does what you want I think: -
DECLARE @Table TABLE(
id INT,
[Time] DATETIME)
INSERT INTO @Table
VALUES (1,
'25 May 2010')
INSERT INTO @Table
VALUES (2,
'24 May 2010')
INSERT INTO @Table
VALUES (3,
'23 May 2010')
INSERT INTO @Table
VALUES (4,
'26 May 2010')
SELECT id,
[Time]
FROM @Table
WHERE [TIME] = ( CONVERT(INT, Getdate(), 112) - 1 )
Returns : -
id Time
----------- -----------------------
1 2010-05-25 00:00:00.000
The bit you want it the WHERE clause.
----EDIT----
As pointed out by 'da-zero', my query above is poorly written. To work with SQL Server, we simply need to change the SELECT part to : -
SELECT id,
[Time]
FROM @Table
WHERE [TIME] = CONVERT(INT, (Dateadd(dd, -1, Getdate())), 112)
However, this doesn't answer the OP because I didn't read the question :blush:
May 26, 2010 at 4:18 am
@skcadavre:
Your solution will work on SQL Server, but not on Access. The getdate() function doesn't exist there.
As the OP proposed, the function DATE() should be used. However, I'm not familiar with Access so I don't know if you can just substract an integer from the date (and still yield correct results).
Furthermore, your solution is flawed as you convert the date to an integer and then substract 1. For example, 01/01/2010 will become with your solution 20100100, which is not desired. In SQL Server, getdate() -1 will work, although I believe that this is not recommended, as it will possibly be deprecated. Beter use dateadd(dd,-1,getdate()), which will always be correct, no matter which version of SQL Server.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 26, 2010 at 4:21 am
da-zero (5/26/2010)
@skcadavre:Your solution will work on SQL Server, but not on Access. The getdate() function doesn't exist there.
As the OP proposed, the function DATE() should be used. However, I'm not familiar with Access so I don't know if you can just substract an integer from the date (and still yield correct results).
Furthermore, your solution is flawed as you convert the date to an integer and then substract 1. For example, 01/01/2010 will become with your solution 20100100, which is not desired. In SQL Server, getdate() -1 will work, although I believe that this is not recommended, as it will possibly be deprecated. Beter use dateadd(dd,-1,getdate()), which will always be correct, no matter which version of SQL Server.
Thanks for the information! 🙂
I didn't consider what'd happen on the 1st of the month, probably should've been the first thought. . . AND I didn't notice the OP mention Access. All in all, I think my post managed to combine my two greatest attributes - unflexible T-SQL and not reading the requirements!
May 26, 2010 at 4:26 am
This is the way to calculate 'yesterday' in an Access query:
DateAdd("d",-1,Date())
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 26, 2010 at 4:39 am
Hi,
Phil Parkin, da-zero, skcadavre
Your query works fine but since Time column has datetime value if i use '=' operator it fetches only rows having time value 25/05/2010 00:00:00.
So if i use '>=' it fetches all the rows of previous day and also the rows upto the current day 3:00 am(if i schedule ssis package to run at that time)
so can i avoid to extract rows from todays date.
Thanks for your help
Sachin
May 26, 2010 at 4:43 am
Hi
All,
I used the below query and its working fine.
SELECT ID, SiteName, DRCNum, TypeID, Point, [Time], CardNumber, FormatName
FROM TransactionLog
WHERE [TIME] >= Format(DateAdd('d', - 1, DATE ()), 'dd/mm/yyyy') AND [TIME] < Format(DATE (), 'dd/mm/yyyy')
Thanks for your help again.
With Regards
Sachin
May 26, 2010 at 4:45 am
sachinrshetty (5/26/2010)
Hi,Phil Parkin, da-zero, skcadavre
Your query works fine but since Time column has datetime value if i use '=' operator it fetches only rows having time value 25/05/2010 00:00:00.
So if i use '>=' it fetches all the rows of previous day and also the rows upto the current day 3:00 am(if i schedule ssis package to run at that time)
so can i avoid to extract rows from todays date.
Thanks for your help
Sachin
Sachin, you've got to start thinking some of this through for yourself. In English:
Date greater than yesterday and less than today, or
Set the time part of 'date' to 0 in your query and then do a direct "=" comparison.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 26, 2010 at 4:46 am
Bravo - you beat me to it 🙂
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 26, 2010 at 4:57 am
To be complete:
this is the second solution Phil spoke of:
SELECT ID, SiteName, DRCNum, TypeID, Point, [Time], CardNumber, FormatName
FROM TransactionLog
WHERE DateAdd('d',DateDiff('d',0,[TIME]),0) = Format(DateAdd('d', - 1, DATE ()), 'dd/mm/yyyy')
The combination of the DateAdd function and the DateDiff function (which I assume exists on Access, I'm too lazy to look it up) will strip the time values of your Time value. This way you can do an exact compare (I believe = in a WHERE clause is faster than using >= and =<, but I can be mistaken).
EDIT: grmbl, why hasn't the code of skcadavre any scrollbars and mine has, while mine is much smaller?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 26, 2010 at 5:20 am
Hi
Phil Parkin and da-zero
May b your second logic will be more effective than mine.
Thank you for solving my issues.
This is second time you both helping me in my work 🙂
Thanks a lot
Sachin
May 26, 2010 at 6:00 am
da-zero (5/26/2010)
EDIT: grmbl, why hasn't the code of skcadavre any scrollbars and mine has, while mine is much smaller?
All about formatting 🙂
E.G. I'd have written your code like so: -
SELECT id,
sitename,
drcnum,
typeid,
point,
[Time],
cardnumber,
formatname
FROM transactionlog
WHERE Dateadd('d', Datediff('d', 0, [TIME]), 0) =
FORMAT(Dateadd('d', -1, DATE ()), 'dd/mm/yyyy')
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply