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]
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply