June 23, 2009 at 4:25 am
Hi All,
Here is my Query,
SET @AnoynomousPlayers=(SELECT isnull(count(*), 0)
FROM tblApplicationPlayer
WHERE (bitAnonymousPlayer = 1) AND (dtApplicationTakenDt = GETDATE() - 1) )
Here the dtApplicationTakenDt is in Datetime and a also GETDATE() is also returning datetime,
What i need is to convert both the date time to just Date format.And want to remove off the time.
So that i can compare only with the date and not the time.
June 23, 2009 at 5:08 am
perhaps do a 'between' statement
e.g
this would bring back everything that had the specified column between
23-06-2009 00:00:00 and 24-06-2009 00:00:00
dtApplicationTakenDt between CONVERT(varchar(50),getdate(),106)
and CONVERT(varchar(50),dateadd(dd,1,getdate()),106)
June 23, 2009 at 5:25 am
Converting to varchar's one of the the slowest ways to trim the time from a date. Also watch out for the edge cases. Between's inclusive on both sides. If a query needs all rows from yesterday, it shouldn't return rows from today at midnight.
This should work.
WHERE dtApplicationTakenDt >= dateadd(dd, datediff(dd,0, GETDATE())-1,0) AND < dateadd(dd, datediff(dd,0, GETDATE()),0)
See http://sqlinthewild.co.za/index.php/2007/11/05/datetime-manipulation/ and http://sqlinthewild.co.za/index.php/2008/09/04/comparing-date-truncations/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 23, 2009 at 5:25 am
add this : select rtrim(cast(GETDATE() as varchar(12)))
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 23, 2009 at 6:58 pm
Gail's method has the distinct advantage of being able to seek using an index. The other methods would have to scan the table or the whole index.
There was a slight typo in her example; this is the correct statement:
[font="Courier New"]WHERE dtApplicationTakenDt >= dateadd(dd, datediff(dd,0, GETDATE())-1,0) AND dtApplicationTakenDt < dateadd(dd, datediff(dd,0, GETDATE()),0)
[/font]
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 23, 2009 at 7:12 pm
hi_abhay78 (6/23/2009)
add this : select rtrim(cast(GETDATE() as varchar(12)))
Abhay,
1 Using CONVERT with a style parameter when converting date-time to string is more robust and explicit.
2 Your statement does not work correctly when the active language is Russian.
So if one must use a string conversion, something like:
[font="Courier New"]select convert(nchar(10), current_timestamp, 120)[/font]
...would be better.
I can only assume you posted too quickly, since the impressive job title and letters after your name in your signature would suggest that you know better.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply