Select data between [date] and Yesterday 3

[Cloonalt]

I'm trying to write a SQL query that will select data between 2/2/2006 and yesterday - to be run on a daily basis. I have:

SELECT AuditId, Audit_Date AS ADate
FROM dbo.Audit_Table
WHERE Audit_Date BETWEEN (Audit_Date > CONVERT(DATETIME, '2006-02-06 00:00:00', 102) and (DATEDIFF(d, Audit_Date, GETDATE() - 1) = 0)


I get an error - Incorrect syntax near '>'.

Any help with this is appreciated.

 

[jbenson001]

Try:

SELECT     AuditId, Audit_Date AS ADate
FROM         dbo.Audit_Table
WHERE    Audit_Date BETWEEN (CONVERT(DATETIME, '2006-02-06 00:00:00', 102) and (DATEDIFF(d, Audit_Date, GETDATE() - 1) = 0)

Jim

 

[Cloonalt]

Thanks for your help.

Incorrect syntax near the keyword 'and'.

 

[Dashley]

Cloonalt,

It looks like you have one to many or one less prentices ")" than you need.

 

[jbenson001]

Actullay I think you are missing a ")" after the first Convert, just before the AND.

Jim

 

[Cloonalt]

I tried
SELECT AuditId, Audit_Date AS ADate
FROM dbo.Audit_Table
WHERE Audit_Date BETWEEN (Audit_Date > CONVERT(DATETIME, '2006-02-06 00:00:00', 102)) and (DATEDIFF(d, Audit_Date, GETDATE() - 1) = 0)

and got

Incorrect syntax near '>'.

Thanks for your help!

 

[jbenson001]

Scratch that, you need to remove the one just after AND

 

[jbenson001]

Audit_Date BETWEEN (CONVERT(DATETIME, '2006-02-06 00:00:00', 102) and DATEDIFF(d, Audit_Date, GETDATE() - 1) = 0)

 

[vongrunt]

Still not good

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[Cloonalt]

SELECT AuditId, Audit_Date AS ADate
FROM dbo.Audit_Table
WHERE Audit_Date BETWEEN (CONVERT(DATETIME, '2006-02-06 00:00:00', 102) and DATEDIFF(d, Audit_Date, GETDATE() - 1) = 0)


Incorrect syntax near the keyword 'and'.

Thanks.

 

[jbenson001]

Well, the problem is even if you get the syntax correct, yow will still get an error. This is because your first result will be a datatime and the second result will be an integer.

Jim

 

[Cloonalt]

Ahaa! I'll work on that.

Thanks.

 

[jbenson001]

No problem

 

[Cloonalt]

Well, this doesn't error, but it doesn't return any data. Do I have to convert the second result?

Thanks for any help.

SELECT Audit_Id, Audit_Date AS ADate
FROM dbo.Audit_Table
WHERE Audit_Date BETWEEN (CONVERT(DATETIME, '2006-02-06 00:00:00', 102)) and DATEDIFF(d, Audit_Date, GETDATE() - 1)

 

[SQLBill]

Why don't you just change this:

and DATEDIFF(d, Audit_Date, GETDATE() - 1)
--returns the number of days between the Audit_Date and 24 hours prior to this second.

to

and GETDATE() - 1
--returns 24 hours prior to this second.

To better understand what is being returned, try this:

SELECT GETDATE() AS [This Moment],
       DATEDIFF(d, Audit_Date, GETDATE()-1),
       GETDATE()-1

-SQLBill

Posting advice: FAQ481-4875

 

[Cloonalt]

Here's what worked:

SELECT Audit_Id, audit_date from dbo.Audit_Table
WHERE Audit_Date BETWEEN (CONVERT(DATETIME, '2006-02-06 00:00:00', 102)) and GETDATE()-1
order by audit_date

Thanks all for your help

 

[vongrunt]

What if Audit_Date has time?


------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[Cloonalt]

The Audit_Date does return time, e.g.,

2006-02-06 08:00:10.297


Do you see a problem? Thanks.

 

[vongrunt]

Yes.

Suppose now is 10:25AM. GETDATE()-1 returns yesterday 10:25AM. Anything past that time yesterday would be ignored.


------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[SQLBill]

A possible problem...

Let's say GETDATE() returns 2006-03-10 10:26:30.000, then GETDATE()-1 would return 2006-03-09 10:26:30.000. Which wouldn't give you the full day.

Vongrunt has an awesome Date/Time FAQ (FAQ183-5842) which shows how to manipulate/use DATETIME.

Work with this:

WHERE Audit_Date BETWEEN (CONVERT(DATETIME, '2006-02-06 00:00:00', 102)) and (DATEADD(DD, DATEDIFF(DD,0,GETDATE())-1,0))

-SQLBill


Posting advice: FAQ481-4875