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.

 

[vongrunt]

Wouldn't that include only 00:00AM from yesterday?

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

 

[SQLBill]

To make sure you are using the correct DATEADD/DATEDIFF combo...

SELECT DATEADD(DD, DATEDIFF(DD,0,GETDATE())-1,0),
DATEADD(DD, DATEDIFF(DD,0,GETDATE()),0))

Top one returns 2006-03-09 00:00:00.000
Bottom one returns 2006-03-10 00:00:00.000

-SQLBill



Posting advice: FAQ481-4875

 

[vongrunt]

Personally I would use

WHERE Audit_Date >= '20060206' and Audit_Date < dateadd(dd, datediff(dd, 0, getdate()), 0)

or:

WHERE Audit_Date >= '20060206' and Audit_Date < convert(varchar(8), getdate(), 112)

Note >= < instead of BETWEEN; that selects everything greater than start date 00:00AM and less than today.

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

 

[Cloonalt]

Vongrunt, neither of those returned any data.

Thanks for your help.

 

[vongrunt]

Argh... can you post query you used plus some sample rows - AuditId, Audit_Date columns - that should appear in result set?

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

 

[Cloonalt]

Vongrunt,

Audit Date: 2/18/2006 10:16:03 AM - datetime field

Audit_id: an identity field starting with 1

SELECT Audit_Id, audit_date from dbo.Audit_Table
WHERE Audit_Date > '20060206' and Audit_Date < convert(varchar(8), getdate(), 112)

Thanks!

 

[vongrunt]

Works for me, even with > (wrong) used instead of >= :

create table #Audit_Table ( Audit_id int, Audit_Date datetime )
insert into #Audit_Table values (1, '2/18/2006 10:16:03 AM')

SELECT Audit_Id, audit_date from #Audit_Table
WHERE Audit_Date >= '20060206' and Audit_Date < convert(varchar(8), getdate(), 112)

drop table #Audit_Table

The only (last famous word) reason why that shouldn't work is wrong system time at server.

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

 

[Cloonalt]

Okay, thanks for your help. I'll try again.

 

[Cloonalt]

Well,l I created the table and inserted data using your code in a different database on a different server and it worked.

Went back to the server and database where the live data is and it didn't work.

argh again.

 

[SQLBill]

As vongrunt suggested...check the time on the server itself. If it is off, that will throw the script off.

Easy way to check, run this and look at your watch. The times should match (unless your computer is using GMT time or your watch doesn't work):

SELECT GETDATE()

-SQLBill

Posting advice: FAQ481-4875

 

[vongrunt]

And another possibility: Audit_Date on live server is varchar... probably not, but used query should raise no errors in this case.

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

 

[SQLBill]

If Audit_Date is VARCHAR (or someother string datatype) as vongrunt suggests, you may need to start your SELECT with SET DATEFORMAT MDY (if the dates are that format).

SQL Server needs to be told how to interpret date values if they are not DATETIME/SMALLDATETIME or yyyymmdd.

-SQLBill

Posting advice: FAQ481-4875

 

[Cloonalt]

Vongrunt, your query works.

In Query Analyzer, I was switching back and forth between databases via the drop down menu.

That didn't really seem to be refreshing.

When I closed Enterprise Manager, went to the live database, opened Query Analyzer and ran the query, it worked.

That's the only thing I can figure. But I have a query now that returns the correct data.

You guys are great! Thanks for all your help.

 

[SQLBill]

Suggestion: If you work with lots of databases, make USE <dbname> your friend. Include it in all your scripts. Or use the full naming convention <shudder> of database_name.owner.table. Both methods keep you from having the problem of a script running in the wrong database.

-SQLBill

Posting advice: FAQ481-4875

 

[Cloonalt]

Thanks.