SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query multiple fields
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

macca
Posting Yak Master

Ireland
146 Posts

Posted - 10/01/2012 :  08:26:26  Show Profile  Reply with Quote
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 - 10/01/2012 :  08:37:04  Show Profile  Reply with Quote
Select * From tbl1
Where Date1=@Date
or Date2=@Date
or Date3=@Date
or Date4=@Date

Mike
Go to Top of Page

macca
Posting Yak Master

Ireland
146 Posts

Posted - 10/01/2012 :  09:17:28  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/01/2012 :  10:48:20  Show Profile  Reply with Quote
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/

Go to Top of Page

Mike Jackson
Starting Member

37 Posts

Posted - 10/01/2012 :  13:34:14  Show Profile  Reply with Quote
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
Go to Top of Page

lazerath
Constraint Violating Yak Guru

USA
328 Posts

Posted - 10/01/2012 :  14:23:25  Show Profile  Reply with Quote
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.
*/
Go to Top of Page

macca
Posting Yak Master

Ireland
146 Posts

Posted - 10/02/2012 :  10:07:21  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/02/2012 :  10:39:50  Show Profile  Reply with Quote
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/

Go to Top of Page

lazerath
Constraint Violating Yak Guru

USA
328 Posts

Posted - 10/02/2012 :  10:41:07  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/02/2012 :  10:49:02  Show Profile  Reply with Quote
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/

Go to Top of Page

macca
Posting Yak Master

Ireland
146 Posts

Posted - 10/02/2012 :  10:59:35  Show Profile  Reply with Quote
Thanks for all your help guys. This is now working.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/02/2012 :  11:36:37  Show Profile  Reply with Quote
welcome

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

Go to Top of Page

lazerath
Constraint Violating Yak Guru

USA
328 Posts

Posted - 10/02/2012 :  11:38:46  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/02/2012 :  11:47:41  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000