Between use for dates.

Question

Post reply

Between use for dates.

atif saeed khan

SSC Veteran

Points: 278
More actions
November 9, 2006 at 6:42 am

#115260

hi!

i have one table in which i have only one date column which is populating with getdate() function automatically. and with this function sql server store full qualified date and time. now wen i am trying to get rows using between dates range it is not working properly.

select reservation_date from a where reservation_date between @d1 and @d2.

is there any prob in geting records in sql with only date.

Kindest Regards,

Atif Saeed Khan

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Ninja's_RGR'us

SSC Guru

Points: 294069

November 9, 2006 at 6:47 am

#670825

That should work. But keep in mind that if @d2 = '2006/11/15' and that reservations are made for '2006/11/15 15:00:00.000'. Then the latter date will not be found by the query (which is the correct behavior).

Vladan SSC-Insane Points: 22072 More actions · Answer 2

What do you mean by "only date"? If what you mean is "date without time portion" then you have to be careful - as Remi (Ninja) wrote in previous post. Datetime value ALWAYS contains time... even if that time should be 00:00:00.000. You can not compare two datetime values without taking that into account.

Although it is possible to use BETWEEN with datetime columns/values, I very much prefer not using it here. It is easier to construct the condition like this:

WHERE reservation_date >= @d1 AND reservation_date <@d2

If you want to find reservations from the time between 1.11.2006 and 7.11.2006 (DD.MM.YYYY, both limits included), you have to set the variables to:

SET @d1 = '20061101'

SET @d2 = '20061108'

Mark that you have to add one day to the upper limit - because with < this date is not included - but all the reservations from the 7.11. will be included, whereas with '20061107' they will be missing... because the date of reservation is actually higher (by some hours and minutes).

/EDIT:

If you want to use BETWEEN, you'd have to scale the date up, too :

WHERE reservation_date BETWEEN @d1 AND @d2

requires you to input @d2 like this:

SET @d2 = '20061107 23:59:59.998'

Now tell me, isn't the first option better?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Ninja's_RGR'us

SSC Guru

Points: 294069

November 9, 2006 at 8:50 am

#670867

Ninja raises his hand and shakes frantically waiting for the teacher to let him answer the question .

Len Bearse SSC Journeyman Points: 79 More actions · Answer 4

atif saeed khan,

I find converting the true date to a varchar helps when searching for date with between function. Typically your as to find dates between mm/dd/yyyy without time. Storing dates in full format which is encouraged poses issues with WHERE clauses.

Timeless Example

Declare @T1 varchar(10), @T2 varchar(10)

Select @T1 = '20061101', @T2 = '20061109' -- Find records from 11/01/06 - 11/09/06

-- Select convert(Varchar(10), getdate(), 112) -- To see the converted date of getdate()

Select * From table

where convert( varchar(10), DateField,112 ) between @T1 and @T2

Make Sense??????

Note: Convert 112 works with datetime datatypes.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

Len, the following will prevent an index from being used if one is available...

where convert( varchar(10), DateField,112 ) between @T1 and @T2

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

CodeLake SSCrazy Points: 2082 More actions · Answer 6

We have a similar situation and we use this.

IF EXISTS(SELECT [name] FROM [sysObjects] WHERE [name] = 'DateToDay' AND [type] = 'FN')

BEGIN

DROP FUNCTION [DateToDay]

END;

GO

CREATE FUNCTION DateToDay(@InDate DATETIME)

RETURNS INTEGER

AS

BEGIN

DECLARE @OutDay INTEGER;

--

SET @OutDay = CAST(DATEPART(YYYY, @InDate) AS INTEGER) * 10000

+ CAST(DATEPART(MM, @InDate) AS INTEGER) * 100

+ CAST(DATEPART(DD, @InDate) AS INTEGER);

--

RETURN @OutDay;

END

GO

where dbo.DateToDay(DateField) between @T1 and @T2

Kindest Regards,

WRACK
CodeLake

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

Careful... that, too, will likely cause a table scan because the column in the WHERE clause is contained in a formula...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

Folks, go back and read Vladan's post... that's the only correct way to do it. He explains "why" quite nicely, too.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Vladan SSC-Insane Points: 22072 More actions · Answer 9

Wrack,

originally, before I found out how to work with DATETIME values in SQL Server to gain best performance, I was using UDF to strip off time portion, too. It is not a good idea to use UDF, and it is not a good idea to use conversion to VARCHAR (not even without UDF). Of course, if your only concern is to get a correct result, you can do this... but performance will suffer greatly.

Why? Because it forces scans instead of index seek, because UDF leads to cursor-like row by row execution instead of set-based, and because conversions use resources that wouldn't be needed in other approach. And one more point : if you learn how to do this without UDF, you can do it in any database. With UDF, you have to create this function before you can work with it. If you go to work for another company, they may have a similar UDF with a different name - or worse, a different UDF with the name you were using for this one.

I know what you probably think now : it works for me, I'm used to it, it would be lots of work to change it everywhere. At least I thought that when I first read a similar post a few years ago. But I began thinking about it, and finally realized that I have no arguments for it except "I'm used to it and I'm lazy to change it". That's hardly enough - so I learned to do it properly and corrected it everywhere in the code.

You can get anything you need, using inbuilt functions DATEADD, DATEDIFF and DATEPART : date without time, first day of next month, last day of previous month and so on. These functions have also better performance than conversion to varchar (I didn't test it myself, but several experts confirmed this). It is up to you to decide whether you want to use these possibilities or stay with your current UDF - I just wanted to explain why I think you should use them. If you have any questions, fire!

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

There is another problem with Wrack's function, as well... it returns the date as an Integer... if you want to compare the date it returns to a DATETIME column, you must convert it to CHAR and then to DATETIME to keep from getting an incorrect answer or an overflow.

I've done a fair bit of testing on this particular problem of producing a "Date with no time"... Vladan is, once again, spot on about the performance. If you'd like to try it out yourself, you'll need some test data... the following code produces a million row test table with random dates and other goodies... (no index on the ADate column, just a Primary Key on the RowNum)...

--===== Create and populate a million row test table to demonstrate the

-- the power of SQL as compared to an "application". A "real life"

-- example would be much wider but this will suffice for test data

-- and is easy to write a comparison test for in an "application".

SELECT TOP 1000000

IDENTITY(INT,1,1) AS RowNum,

CAST(RAND(CAST(NEWID() AS VARBINARY))*50000+1 AS INT) AS UserID,

CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65))

+ CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65)) AS SomeValue,

'A column for kicks' AS Kicks,

'Still another column just for proofing' AS StillAnother,

CAST(RAND(CAST(NEWID() AS VARBINARY))*100 AS DECIMAL(18,9)) AS SomeNumber,

CAST(RAND(CAST(NEWID() AS VARBINARY))*3653.0+36524.0 AS DATETIME) AS ADate --(>=01/01/2000 <01/01/2010)

INTO dbo.BigTest

FROM Master.dbo.SysColumns sc1,

Master.dbo.SysColumns sc2

--===== A table is not properly formed unless a Primary Key has been assigned

ALTER TABLE dbo.BigTest

ADD PRIMARY KEY CLUSTERED (RowNum)

... and here's some code to test the run duration of the various methods of stripping time from the date (converting date to whole day)... the only rule for the test is that each method must return a DateTime data type to simulate any implicit conversions that will occur when comparing to a DateTime data column (AND, you should always store dates and times as a DATETIME data type for a lot of reasons, not the least of which is performance)...

--===== Declare a couple of operating variables for the test

DECLARE @MyDate DATETIME --Holds the result of a conversion to bypass display times

DECLARE @StartTime DATETIME --For measuring duration of each snippet

PRINT '===== Rounding method 2 ======'

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

SET @StartTime = GETDATE()

SELECT @MyDate=CAST(CAST((ADate - 0.5 ) AS INTEGER) AS DATETIME) FROM dbo.BigTest

PRINT STR(DATEDIFF(ms,@StartTime,GETDATE()),10) + ' Milliseconds'

PRINT ' '

PRINT '===== DateDiff/DateAdd method ======'

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

SET @StartTime = GETDATE()

SELECT @MyDate=DATEADD(dd,DATEDIFF(dd,0,ADate),0) FROM dbo.BigTest

PRINT STR(DATEDIFF(ms,@StartTime,GETDATE()),10) + ' Milliseconds'

PRINT ' '

PRINT '===== Rounding method 1 ======'

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

SET @StartTime = GETDATE()

SELECT @MyDate=CAST(ROUND(CAST(ADate AS FLOAT),0,1) AS DATETIME) FROM dbo.BigTest

PRINT STR(DATEDIFF(ms,@StartTime,GETDATE()),10) + ' Milliseconds'

PRINT ' '

PRINT '===== Convert method ================'

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

SET @StartTime = GETDATE()

SELECT @MyDate=CAST(CONVERT(CHAR(8),ADate,112) AS DATETIME) FROM dbo.BigTest

PRINT STR(DATEDIFF(ms,@StartTime,GETDATE()),10) + ' Milliseconds'

PRINT ' '

PRINT '===== Wrack''s Integer function ====='

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

SET @StartTime = GETDATE()

SELECT @MyDate=CAST(CAST(dbo.DateToDay(Adate) AS CHAR(8)) AS DATETIME) FROM dbo.BigTest

PRINT STR(DATEDIFF(ms,@StartTime,GETDATE()),10) + ' Milliseconds'

PRINT ' '

... and, for those that don't want to take the time to run setup and run this test... here's the run results...

===== Rounding method 2 ======