select date
-
Hi All,
I want to select specific date from one table, the table it self having date column and datatype is datetime (8).
I tried convert and cast function but thos are not working.
Please help me coz most of the reports I have to run on specific date.
Thanks
-
What exactly does not work?
What are trying to do?
Any exaple of your code having problem?
_____________
Code for TallyGenerator -
Your problem, I assume, is that you have a datetime column - note that it contains both a DATE and a TIME value. If you have your where clause resembling
where MyDateCol = '2006-03-26' then SQL Server interprets this as
where MyDateCol = '2006-03-26 00:00.00' - ie, Midnight.
Quite often the data in your column will represent dates & times such as '2005-03-26 15:25.18' for example. The trick is to ensure that you cover the RANGE of datetime values for the day in question, so restructure your where clause as
where MyDateCol BETWEEN '2006-03-26 00:00.000' and '2006-03-26 23:59.997'
SQL Server is accurate to the 3ms mark on dates & times so the time 23:59.997 is the latest possible time of the day.
Hope this helps
PS - I agree with the other responder (who must've responded whilst I was writing this - you need to provide a clearer picture of what is wrong - I've assumed this answers your question as it's probably the most common datetime question)
-
Yeah Ian you are right it was as simple as to forget, now I can get my data using between clause.e.g
tablename = mytransaction
columnname = date
datatype = datetime
select * from mytransaction where date between
'2006-03-25' and '2006-03-26'
Thanks for your reply
-
I'd just like to add my two cents.
Zia method's is the one I use. If you attempt a date convert on the where clause, you will cause a sql to perform a full table scan.
-
select * from Table where convert(char(11),columnname,111) = 'datevalue' between 'datevalue'
With regards,
Karthikeyan
-
Be careful with your statment ...
between '2006-03-25' and '2006-03-26'
if a record on 3/26 is added at exactly midnight + 1 msec, then you will get it. between is inclusive (2006-03-06 00:00:00 will qualify), then you will actually have 2 dates on your report.
oh .. and if you dont think it will *ever* happen, it ususally does ..
-
Between can be dangerous for the reasons stated. Given the column value is a datetime (or smalldatetime for that matter), I believe using DateDiff() is going to offer the best performance and clearest results. In the case of the dates used in the example (and to get your granularity of results only down to the date and not the time):
WHERE DateDiff(dd,'2006-03-25',<ColumnName> ) = 0 --Only stuff from March 25
If you wanted a range of dates:
WHERE DateDiff(dd,'2006-03-25',<ColumnName> ) >= 0
AND DateDiff(dd,'2006-04-01',<ColumnName> ) <= 0 --Stuff from March 25 through April 1, inclusive.
You could of course substitute variables for the dates!
M2C,
--Sauron--
P.S. Hey, what happened to the code tag in the editor?
-
I believe using DateDiff() is going to offer the best performance and clearest results.
Clearest results possibly, but very definately not best performance. Be very careful when applying any function to a column in the where clause of a query. Any form of function will prevent SQL from using an index to do the filter and in many cases will force a full table scan. If your table is 100 rows that won't be a problem, if it's 100 thousand however, you will have a performance nightmare.
The best (performnce-wise) way of getting all the records for a specific day when the date is stored as a full date time is the following
DECLARE @DesiredDate DATETIME
SET @DesiredDate = '2006/03/27' -- you want all records from the 27th and none from any other date.
SELECT <fields> from tbl where <DateColumn> BETWEEN @DesiredDate AND DATEADD(ms,-3,DATEADD(dd,1,@DesiredDate))
This will get all records where the value in the date column is between 2006/03/27 00:00:00.000 and 2006/03/27 23:59:59.997 (adding 1 day, then subtracting 3 milliseconds from the specified starting date)
Since datetimes only have a granularity of 3 milliseconds, anything after 2006/03/27 23:59:59.997 will be on the 28th.
If you want the data from 2 days, then use teh dateadd to add 2 days, not 1, etc.
Hope that helps.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I agree with you there Gila.
Since 'a between b and c' is interpreted as 'b <= a and a <= c' (I believe - and assuming b <= c), I think this will yield near identical performance:
SELECT <fields> from tbl where @DesiredDate <= <DateColumn> AND <DateColumn> < DATEADD(dd,1,@DesiredDate)
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Hi,
Use this Query i think it would help you to remove the time part in your dates ::
SELECT convert(datetime,convert(char(12),GETDATE(),111))
Regards,
Amit Gupta
-
Gila says:
Clearest results possibly, but very definately not best performance. Be very careful when applying any function to a column in the where clause of a query. Any form of function will prevent SQL from using an index to do the filter and in many cases will force a full table scan.
*******************************************
I just want to give a great big 'shout-out' to Gila for the information about avoiding the use of functions in where clauses. I have been trying to improve the performance of a query for days without success, but after implementing the suggestion given by Gila, the performance of the query improved from 25 seconds to 1 second!
-
Hear, hear!
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
I cannot believe the number of people who advocate functions on columns in where clauses...
Think in terms of your white pages - if you wanted to find names starting with 'A' that are 5 letters in length, you would use the INDEX and get the names starting with 'A' and then check them for 5 letters in length, rather than the other way around... Not really a good example I 'spose but should demonstrate the point 
And as for BETWEEN, that's why I clearly said that the upper bound should be .997 off from the next day. For the pedants, you could use
MyDateCol >= '2006-03-26' and MyDateCol < '2006-03-27' (note <, not <=)
That might straigten things out.
Sorry for rant - sprained wrist today and typing is now annoying -
I just want to give a great big 'shout-out' to Gila for the information about avoiding the use of functions in where clauses.
My pleasure.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply