January 8, 2009 at 12:20 pm
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
January 8, 2009 at 12:25 pm
There are several ways to strip the time out, here is one:
select cast(floor(cast(getdate() as Float)) as datetime)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 8, 2009 at 12:32 pm
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.
January 8, 2009 at 12:36 pm
The "DATEDIFF" method is supposed to be the fastest, but I can never remember it off the top of my head.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 8, 2009 at 12:40 pm
Figured it out:
Select DATEADD(dd,DATEDIFF(dd,0, getdate()), 0)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 8, 2009 at 10:11 pm
If the following formula results in zero, the data was entered today, regardless of time...
SELECT DATEDIFF(dd,datecolumn,GETDATE())
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy