Date and Time Issue in script - removing time and leaving date only

Question

Post reply

Date and Time Issue in script - removing time and leaving date only

rons-605185

SSCommitted

Points: 1618
More actions
November 19, 2009 at 10:15 am

#216827

Using SQL 2005 and I have a table called notes with a field EntryDate in Datetime format.
Attempting to create a script that will show only those notes whose entrydate is x days old from today. For this example 10 days old from today (11-19-2009). I understand that I need to 'strip' the time portion of the datetime, leaving only the actual date and then work from there. I need to do this for today's date = getdate(); the @Date which is today's date - 10 days and the EntryDate in the Notes table.
I think I have the getdate() set to date only and same for the @Date. I cannot get the entrydate converted to date only.
How do I get the 3 datetime fields converted to date only (no time) and then show only notes where entrydate is 10 days old from today?
See code below:
CODE:
DECLARE
@Date DateTime
, @TodayDateTime
, @EntryDate DateTime
, @entrydateconv datetime -- temp
SET @Today = dateadd(day,datediff(day,0,getdate()),0)
SET @Date = DATEADD(Day, -10, @Today)
BEGIN
Select @entrydate = dbo.NOTE_tblNote.entrydate
from dbo.NOTE_tblNote
Set @entrydateconv = dateadd(day,datediff(day,0,@entrydate),0)
print 'entry date'
print @entrydate
print 'converted entry date'
print @entrydateconv
END
SELECT
@entrydateconv as conventrydate
, n.entrydate
, n.notetype
, i.employeename
, i.title
, i.emailaddress
, x.companyname
, c.firstname as 'Contact First Name'
, c.middleinit as 'Contact Middle Initial'
, c.lastname as 'Contact Last Name'
, n.companyid as note_co_id
, n.employeeid as note_empl_id
, i.employeeid as icomp_empl_id
FROM dbo.NOTE_tblNote n, dbo.ICOMP_tblEmployee i, XCOMP_tblCompany x, CON_tblContact c
WHERE
@entrydateconv = @Date
and n.employeeid = i.employeeid
and n.companyid = x.companyid
and n.contactid = c.contactid
ORDER BY by i.employeename, x.companyname, n.entrydate

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

The best way I know of to strip out the time from date is;

SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)

to show data from 10 days ago you can use the DATEADD function

where YourDate <= DATEADD(day,-10,getdate())

or

where YourDate <= GETDATE() - 10

Lowell SSC Guru Points: 323518 More actions · Answer 2

the way i usually do it is like this, which keeps everything in the same datetime datatype with no conversions, and effectively strips/zeros the time portion:

--Midnight for the Current Day

select DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)

in a million+row operation, i seem to remember Gail Shaw

showing an example where it was slightly faster than using the floor operation

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass