Between use for dates.
-
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
-
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).
-
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 raises his hand and shakes frantically waiting for the teacher to let him answer the question
. -
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.
-
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) -
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 -
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) -
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) -
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!
-
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 ======
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
2656 Milliseconds
===== DateDiff/DateAdd method ======
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
2893 Milliseconds
===== Rounding method 1 ======
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
3576 Milliseconds
===== Convert method ================
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
5670 Milliseconds
===== Wrack's Integer function =====
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
14860 Milliseconds
Do keep in mind that Vladan's original post correctly shows how to use criteria to find a range of dates... when used on a column in a WHERE clause, all of the formulas above guarantee that an Index Seek will NOT be used even when the table is correctly indexed for max performance on such a query.
--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) -
hm thats prob i think dear. ok tell me is there any way that i can add rows with getdate just the date only. not time. time shud b zero only. wat u say ? actually im populatin my table throu while loop and making calendar of reservation dates. so this date is really making me crazy now
help me dude if u can. thankx.Kindest Regards,
Atif Saeed Khan
-
That should do it (untested!?!?)
SELECT DATEADD(D,0, DATEDIFF(D, 0, GETDATE()))
-
Actually, I'm using
DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0)
but both ways result is the same - it is like 5+3 or 3+5. Tested.
-
me always love to write all my business logics in sp only.
I'll probably get some flack for this... bit I'm with you...
--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)
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply