August 3, 2004 at 12:22 pm
How to select records using just the date and not the time; I want to show all records with a date of '07/16/2004'
I have a table with a field cTime attribute datetime; the values are similar to the following: 7/16/2004 8:50:54 AM.
August 4, 2004 at 12:28 am
Two ways, the second may prevent the use of indexes, so be warned.
DECLARE @dt DATETIME
SET @dt = '2004/07/16'
SELECT * FROM tbl
WHERE cTime BETWEEN @dt AND DATEADD(ss,-1,DATEADD(dd,1,@dt))
(all records where cTime between 2004/07/16 00:00:00 and 2004/07/16 23:59:59)
OR
SELECT * FROM tbl
WHERE CAST(FLOOR(CAST cTime AS FLOAT)) AS DATETIME) = @dt
(All records where the date portion of cTime = 2004/07/16)
HTH
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
August 4, 2004 at 6:08 am
There are 2 methods I have used to do this....
1. Convert the field to a varchar and then do the comparision.
Select * From Table Where Convert(VarChar(10), MyField, 101) = '08/04/2004'
When using this method, you will need to be very careful about the formatting of the "constant" part of the expression.
2. When using large tables, I opted to add another field to the table that stores the date only (the time part it 00:00:00). I know this violates database normalization practices, but it really sped up my queries.
George Mastros
Orbit Software, Inc.
August 4, 2004 at 7:26 am
If you are looking for a single date, you can also use
datediff(d,'08/04/2004',myfield) = 0.
This also allows you to select current (todays) records by
datediff(d,myfield,getdate()) = 0
or even those in the last five minutes
datediff(mi,myfiled,getdate()) <= 5
Jonathan
August 4, 2004 at 8:05 am
I always use the cast function for this.
select cast(left(order_date,11) as datetime)
This eliminates the time.
August 4, 2004 at 8:13 am
Thanks for all the information!
I was trying to use a statement that I can use on an AS400:
select * from cptipdta/seams where date(ctime)='2004-05-17'
but that does not work on a SQL Server and was looking for something not similar that was not complicated.
The "(CAST(LEFT(cTime, 11) AS datetime) = '2004-05-17')" works just fine for me...Thanks again everyone!
August 16, 2004 at 4:40 am
I have these function also which get date only
http://www.sqlservercentral.com/scripts/contributions/733.asp
Date without Time (2 functions)
Some time you need to know only Date without Time.
This is simple function that allows you truncate datetime field to date only.
This function can help you to construct dynamic T-SQL for select date period.
P.S. Thanks for ispaleny!
Conversion only for real not enough! It is necessary convert to real that correct errors near the last instant of the day.
--------------------------------------------
CREATE FUNCTION DateOnly_sdt
(@DateAndTime AS smalldatetime)
RETURNS smalldatetime
AS
/*****************************************
* return date without time smalldatetime
*****************************************/
BEGIN
RETURN CAST(ROUND(CAST(@DateAndTime AS float),0,1) AS smalldatetime)
END
CREATE FUNCTION DateOnly_dt
(@DateAndTime AS datetime)
RETURNS datetime
AS
/*****************************************
* return date without time for datetime
*****************************************/
BEGIN
RETURN CAST(ROUND(CAST(@DateAndTime AS float),0,1) AS datetime)
END
another Function
----------------------------------------------
CREATE function getdatePortion(@inDate datetime)
returns datetime
As
begin
return convert(datetime,convert(varchar(11),@inDate))
end
I hope this help u
Alamir Mohamed
Alamir_mohamed@yahoo.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply