Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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: Tuesday, April 26, 2016 6:40 AM
Points: 376, Visits: 284
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, September 22, 2016 9:30 AM
Points: 9,298, Visits: 9,516
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: Wednesday, February 10, 2016 7:22 AM
Points: 202, Visits: 1,155
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, September 22, 2016 9:30 AM
Points: 9,298, Visits: 9,516
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, September 22, 2016 9:30 AM
Points: 9,298, Visits: 9,516
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-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:47 PM
Points: 41,426, Visits: 38,755
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."

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

Add to briefcase

Permissions Expand / Collapse