SP - SELECT date between, either, or all

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

General SQL Server Forums

New to SQL Server Programming

SP - SELECT date between, either, or all

Author

Topic

calvinfoo
Posting Yak Master

129 Posts

Posted - 2012-08-06 : 23:02:08

Dear Gurus,
Condition as this:
User pass in two dates - Date_From and Date_To
Return the records based on the selected Date

Three possible scenarios:
1. Both are NOT NULL,
THEN select the date BETWEEN

2. Either one IS NULL,
THEN select one of the selected date

3. Both IS NULL,
THEN select all records

My idea and solution (Not working of course), please advise:

DECLARE @date_from datetime
DECLARE @date_to datetime

CASE 
  WHEN @date_from IS NULL AND @date_to IS NOT NULL THEN @date_from = @date_to

  WHEN @date_from IS NOT NULL AND @date_to IS NULL THEN @date_to = @date_from

END

SELECT * FROM myTable WHERE
RecordDate BETWEEN @date_from AND @date_to

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2012-08-07 : 00:52:25

There are probably a few ways to do this and I'm sure you could embed what you want into one SELECT statement, but I would personally avoid it. You could try this...if you take the SET statements out, one or both, you'll see that you return different values based on whether or not the date_from and date_to are null:

DECLARE @Date_From DateTime
,@Date_To DateTime

SET @Date_From='2012-07-01'
SET @Date_To='2012-07-03'

declare @MyTable Table (Id int, RecordDate datetime)

INSERT INTO @MyTable (Id, RecordDate)
VALUES
(1, '2012-07-01'),
(2, '2012-07-02'),
(3, '2012-07-03'),
(4, '2012-07-04')

IF @Date_From IS NULL AND @Date_To IS NULL
BEGIN
SELECT ID, RecordDate
FROM @MyTable
END
ELSE

IF @Date_From IS NOT NULL AND @Date_To IS NULL
BEGIN
SELECT ID, RecordDate
FROM @MyTable
WHERE RecordDate>=@Date_From
END
ELSE

IF @Date_From IS NULL AND @Date_To IS NOT NULL
BEGIN
SELECT ID, RecordDate
FROM @MyTable
WHERE RecordDate<=@Date_To
END
ELSE

IF @Date_From IS NOT NULL AND @Date_To IS NOT NULL
BEGIN
SELECT ID, RecordDate
FROM @MyTable
WHERE RecordDate>=@Date_From AND RecordDate<=@Date_To
END

EDIT: Got rid of the splot!

calvinfoo
Posting Yak Master

129 Posts

Posted - 2012-08-07 : 01:25:19

That is pretty messy solution I must say. hehehe... actually I have more variables than just Date_from and Date_to, that will be exponential long list if I would follow your method. Any simpler solution?

if using ASP as example, I would need something like this:

if date_from is null and date_to is not null then date_from = date_to
if date_from is not null and date_to is null then date_to = date_from
sqlstr = "SELECT * FROM myTable"

if date_from is not null and date_to is not null then 
sqlstr = sqlstr & " WHERE RecordDate BETWEEN '" & date_from & "' AND '" & date_to & "'"
end if

But I am trying to learn and move everything to SP instead of contruct SQL from ASP

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2012-08-07 : 01:46:02

Hm, I don't think it's very messy...actually, it's organized based on your criteria and could be the beginnings of a stored procedure. But if you must embed it as you it appears, you could certainly write a case expression into your where clause. It will perhaps degrade some performance. I'll let someone else advise because I think you're going to subject yourself to SQL injection if you go down the path that it looks like you're heading.

calvinfoo
Posting Yak Master

129 Posts

Posted - 2012-08-07 : 02:02:30

Yeah, SQL inject is the main reason why I wish to move towards SP. Even it is intranet application, I would prefer get it done in SP instead. Also it looks neater to move the query to SQL instead on the ASP, and safer too. If I messed up with my Front-end, at least I am don't have to worry about I accidentally mess up the SQL part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources