Query multiple fields - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query multiple fields

Author

Topic

macca
Posting Yak Master

146 Posts

Posted - 2012-10-01 : 08:26:26

I want to write a select that queries 4 fields in a table. The 4 fields are Date1, Date2, Date3 and Date4.

The query would be something like this:

Select * From tbl1
Where @Date = Date1 or Date2 or Date3 or Date4

Anyone any ideas how to do this?
Thanks.

Mike Jackson
Starting Member

37 Posts

Posted - 2012-10-01 : 08:37:04

Select * From tbl1
Where Date1=@Date
or Date2=@Date
or Date3=@Date
or Date4=@Date

Mike

macca
Posting Yak Master

146 Posts

Posted - 2012-10-01 : 09:17:28

Thanks for the reply.
I am doing it like this though:
a.date BETWEEN @FromDate AND @ToDate

Any ideas how to do it this way.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-01 : 10:48:20

quote:
Originally posted by macca

Thanks for the reply.
I am doing it like this though:
a.date BETWEEN @FromDate AND @ToDate

Any ideas how to do it this way.

this is not equivalent to your initial requirement unless dates are all continuos

even in that case if date has timepart it has to be like

a.date >=@FromDate AND a.date < @ToDate + 1

to include till last minute of ToDate value

see this to understand how date are stored internally in sql

http://visakhm.blogspot.com/2012/07/generate-datetime-values-from-integers.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Mike Jackson
Starting Member

37 Posts

Posted - 2012-10-01 : 13:34:14

Select *
From tbl1
Where (Date1 >=@FromDate AND Date1 < @ToDate + 1)
or (Date2 >= @FromDate AND Date2 < @ToDate + 1)
or (Date3 >= @FromDate AND Date3 < @ToDate + 1)
or (Date4 >= @FromDate AND Date4 < @ToDate + 1)

Mike

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2012-10-01 : 14:23:25

macca, be careful with using code like a.date < @ToDate + 1 without fully understanding what is being passed in. This code will work if you are supplying a date only and want to include all datetime values valid on that date. As an example, let's say:

DECLARE @FromDate DATETIME, @ToDate DATETIME;
SELECT @FromDate ='1/1/2012', @ToDate = '1/31/2012';

DECLARE @SampleDateTable TABLE (Date1 DATETIME PRIMARY KEY);
INSERT @SampleDateTable VALUES('1/1/2012 00:00:00.000');
INSERT @SampleDateTable VALUES('1/1/2012 02:43:40.000');
INSERT @SampleDateTable VALUES('1/16/2012 17:57:02.000');
INSERT @SampleDateTable VALUES('1/31/2012 00:00:00.000');
INSERT @SampleDateTable VALUES('1/31/2012 23:59:59.997');
INSERT @SampleDateTable VALUES('2/1/2012 00:00:00.000');
INSERT @SampleDateTable VALUES('2/12/2012 00:00:00.000');

SELECT * FROM @SampleDateTable;
/*
Date1
2012-01-01 00:00:00.000
2012-01-01 02:43:40.000
2012-01-16 17:57:02.000
2012-01-31 00:00:00.000
2012-01-31 23:59:59.997
2012-02-01 00:00:00.000
2012-02-12 00:00:00.000
*/

SELECT * FROM @SampleDateTable t WHERE t.Date1 > @FromDate AND t.Date1 < @ToDate + 1;
/*
Date1
2012-01-01 02:43:40.000
2012-01-16 17:57:02.000
2012-01-31 00:00:00.000
2012-01-31 23:59:59.997 -- We got the last entry on 1/31, which wouldn't be included if we only used @ToDate without +1.
*/

-- However, if users are passing in times, they may not want the additional day added
SELECT @FromDate ='1/1/2012 5:45 PM', @ToDate = '1/31/2012 5:45 PM';

SELECT * FROM @SampleDateTable t WHERE t.Date1 > @FromDate AND t.Date1 < @ToDate + 1;

/*
Date1
2012-01-16 17:57:02.000
2012-01-31 00:00:00.000
2012-01-31 23:59:59.997
2012-02-01 00:00:00.000  -- Note this now includes 2/1 as the ToDate is effectively 2/1/2012 5:45 PM.  May not be what the user wants.
*/

macca
Posting Yak Master

146 Posts

Posted - 2012-10-02 : 10:07:21

I have used the below which has worked:

Select *
From tbl1
Where (Date1 >=@FromDate AND Date1 <= @ToDate)
or (Date2 >= @FromDate AND Date2 <= @ToDate)
or (Date3 >= @FromDate AND Date3 <= @ToDate)
or (Date4 >= @FromDate AND Date4 <= @ToDate)

But the problem I now have is that I want to add the following line to the sql code:

AND Area = @Area

The code will look like:

Select *
From tbl1
Where (Date1 >=@FromDate AND Date1 < @ToDate + 1)
or (Date2 >= @FromDate AND Date2 < @ToDate + 1)
or (Date3 >= @FromDate AND Date3 < @ToDate + 1)
or (Date4 >= @FromDate AND Date4 < @ToDate + 1)
And Area = @Area

This throws an error though, can anyone point out what's wrong with this?

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-02 : 10:39:50

it should be


Select * 
From tbl1 
Where ((Date1 >=@FromDate AND Date1 < @ToDate + 1)
or (Date2 >= @FromDate AND Date2 < @ToDate + 1)
or (Date3 >= @FromDate AND Date3 < @ToDate + 1)
or (Date4 >= @FromDate AND Date4 < @ToDate + 1))
and Area = @Area

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2012-10-02 : 10:41:07

You will have to check your schema as the syntax is correct. Simply converting tbl1 to a table variable with matching schema works:

declare @tbl1 table (date1 datetime, date2 datetime, date3 datetime, date4 datetime, Area varchar(50))
declare @FromDate datetime, @ToDate datetime, @area varchar(50);

Select * 
From @tbl1 
Where (Date1 >=@FromDate AND Date1 < @ToDate + 1)
or (Date2 >= @FromDate AND Date2 < @ToDate + 1)
or (Date3 >= @FromDate AND Date3 < @ToDate + 1)
or (Date4 >= @FromDate AND Date4 < @ToDate + 1)
And Area = @Area

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-02 : 10:49:02

quote:

Originally posted by lazerath

You will have to check your schema as the syntax is correct. Simply converting tbl1 to a table variable with matching schema works:

declare @tbl1 table (date1 datetime, date2 datetime, date3 datetime, date4 datetime, Area varchar(50))
declare @FromDate datetime, @ToDate datetime, @area varchar(50);

Select * 
From @tbl1 
Where (Date1 >=@FromDate AND Date1 < @ToDate + 1)
or (Date2 >= @FromDate AND Date2 < @ToDate + 1)
or (Date3 >= @FromDate AND Date3 < @ToDate + 1)
or (Date4 >= @FromDate AND Date4 < @ToDate + 1)
And Area = @Area

might need a set of braces around date comparison conditions else Area filter will get bypassed if any of them holds good

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

macca
Posting Yak Master

146 Posts

Posted - 2012-10-02 : 10:59:35

Thanks for all your help guys. This is now working.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-02 : 11:36:37

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2012-10-02 : 11:38:46

quote:
Originally posted by visakh16

quote:
Originally posted by lazerath

You will have to check your schema as the syntax is correct. Simply converting tbl1 to a table variable with matching schema works:

declare @tbl1 table (date1 datetime, date2 datetime, date3 datetime, date4 datetime, Area varchar(50))
declare @FromDate datetime, @ToDate datetime, @area varchar(50);

Select *
From @tbl1
Where (Date1 >=@FromDate AND Date1 < @ToDate + 1)
or (Date2 >= @FromDate AND Date2 < @ToDate + 1)
or (Date3 >= @FromDate AND Date3 < @ToDate + 1)
or (Date4 >= @FromDate AND Date4 < @ToDate + 1)
And Area = @Area

might need a set of braces around date comparison conditions else Area filter will get bypassed if any of them holds good

Absolutely, but that doesn't account for the error that was being thrown.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-02 : 11:47:41

quote:
Originally posted by lazerath

quote:
Originally posted by visakh16

quote:
Originally posted by lazerath

You will have to check your schema as the syntax is correct. Simply converting tbl1 to a table variable with matching schema works:

declare @tbl1 table (date1 datetime, date2 datetime, date3 datetime, date4 datetime, Area varchar(50))
declare @FromDate datetime, @ToDate datetime, @area varchar(50);

Select *
From @tbl1
Where (Date1 >=@FromDate AND Date1 < @ToDate + 1)
or (Date2 >= @FromDate AND Date2 < @ToDate + 1)
or (Date3 >= @FromDate AND Date3 < @ToDate + 1)
or (Date4 >= @FromDate AND Date4 < @ToDate + 1)
And Area = @Area

might need a set of braces around date comparison conditions else Area filter will get bypassed if any of them holds good

Absolutely, but that doesn't account for the error that was being thrown.

yep...That error has nothing to do with this

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources