Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Datetime Checks Expand / Collapse
Author
Message
Posted Thursday, January 8, 2009 12:20 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 19, 2013 11:03 AM
Points: 374, Visits: 280
I have a table with a column as dateentered with datatype varchar(10). I want to check this column with getdate(). Now since getdate() return date with time. and if i do cast(dateenetered as datetime) - this return only date with tiem as 00:00:00. How is it possibel to check only the date part ex if dateentered values are

2009-01-07 00:00:00.000
2009-01-07 00:00:00.000
and if select getdate() returns 2009-01-08 14:42:15.613
i need to check only 2009-01-07 with 2009-01-08 ignoring the time. ANy help on this will be greatly appreciated. TIA



Post #632737
Posted Thursday, January 8, 2009 12:25 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
There are several ways to strip the time out, here is one:
select cast(floor(cast(getdate() as Float)) as datetime)



-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #632741
Posted Thursday, January 8, 2009 12:32 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:47 PM
Points: 202, Visits: 1,097
RBarryYoung (1/8/2009)
There are several ways to strip the time out, here is one:
select cast(floor(cast(getdate() as Float)) as datetime)



I had always used
select cast(convert(char(10), getdate(), 101) as datetime)

but this way is more slick.
Post #632748
Posted Thursday, January 8, 2009 12:36 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
The "DATEDIFF" method is supposed to be the fastest, but I can never remember it off the top of my head.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #632753
Posted Thursday, January 8, 2009 12:40 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
Figured it out:
Select DATEADD(dd,DATEDIFF(dd,0, getdate()), 0)



-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #632758
Posted Thursday, January 8, 2009 10:11 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 37,102, Visits: 31,655
If the following formula results in zero, the data was entered today, regardless of time...

SELECT DATEDIFF(dd,datecolumn,GETDATE())


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #633077
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse