| Author |
 Topic  |
|
|
macca
Posting Yak Master
Ireland
137 Posts |
 Posted - 10/01/2012 : 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 - 10/01/2012 : 08:37:04
|
Select * From tbl1
Where Date1=@Date
or Date2=@Date
or Date3=@Date
or Date4=@Date
Mike |
 |
|
|
macca
Posting Yak Master
Ireland
137 Posts |
Posted - 10/01/2012 : 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
India
47189 Posts |
Posted - 10/01/2012 : 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 - 10/01/2012 : 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
USA
278 Posts |
Posted - 10/01/2012 : 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
Ireland
137 Posts |
Posted - 10/02/2012 : 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
India
47189 Posts |
Posted - 10/02/2012 : 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
USA
278 Posts |
Posted - 10/02/2012 : 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
India
47189 Posts |
Posted - 10/02/2012 : 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
Ireland
137 Posts |
Posted - 10/02/2012 : 10:59:35
|
| Thanks for all your help guys. This is now working. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47189 Posts |
Posted - 10/02/2012 : 11:36:37
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
lazerath
Constraint Violating Yak Guru
USA
278 Posts |
Posted - 10/02/2012 : 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
India
47189 Posts |
Posted - 10/02/2012 : 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/
|
|
|
| |
Topic |
|
Query multiple fields
