GetDate ?
-
Hi all, I seem to be experiencing problems with a view i've written in SQL server. I have put this view together...
"SELECT TOP 100 PERCENT data.tblUser.UserN, data.indRoom.Room, data.tblBooking.TimeStampT, data.tblBooking.DateD, data.tblBooking.TimeT,
data.tblBooking.Projector, data.tblBooking.Laptop, data.tblBooking.Network, data.tblBooking.IDbk, data.tblBooking.Video, data.tblBooking.TimeF
FROM data.indRoom INNER JOIN
data.tblBooking ON data.indRoom.ID = data.tblBooking.Room INNER JOIN
data.tblUser ON data.tblBooking.Staff = data.tblUser.UsrID
WHERE (data.tblBooking.DateD >= GETDATE())
ORDER BY data.tblBooking.DateD"
Yes it will not display an entry on todays date, the 4th, it will only show from the 5th onwards. I have checked the clock on the SQL server and it is correct so I suspect it has something to do with the Getdate function ?
Any help would be greatly apreciated,
Mitch.....
-
The getdate() function will display date and time. For example 1/4/2005 10:40:00.00, so when you run the query with the >= the current day (1/4/05) will be excluded. You will need to convert the getdate() to a day only with the datepart function to resolve the issue.
hope this helps
-
Cheers. I've never used DatePart but I shall try this method now.
Thanks again.
-
Here goes:
select cast(datepart(month,getdate())as varchar)+'/'+cast(datepart(day,getdate())as varchar)+'/'+cast(datepart(year,getdate())as varchar)
-
Excellent, thats taken care of it.
Thanks again !!
-
Ok, I understand the mentality of BRUTE forcing GETDATE() apart to be just a DATE but why not use the following?
CONVERT(VARCHAR(10), GETDATE(), 101) ??
And as a bonus WHY is it GETDATE() ???
Because MS was afraid they might be sued if they used GOTDATE()....
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Forgot about the CONVERT and style paramters...good tip.
-
The getdate() function will display date and time. For example 1/4/2005 10:40:00.00, so when you run the query with the >= the current day (1/4/05) will be excluded.
Slightly incorrect!
It will only be excluded when the underlying data do not contain any timeportion apart from midnight. Consider this:
create table #t
(
c1 datetime default dateadd(d,datediff(d,0,getdate()),0)
)
insert into #t default values
select * from #t where c1 > getdate()
drop table #t
c1
------------------------------------------------------
(0 row(s) affected)
The default above strips the time portion out of getdate(), i.e. sets it to midnight. No row is returned. Now we change this a little bit:
create table #t
(
c1 datetime default getdate()+.05
)
insert into #t default values
select * from #t where c1 > getdate()
drop table #t
c1
------------------------------------------------------
2005-01-05 10:53:37.020
(1 row(s) affected)
Here we don't set the time to midnight and add a small portion to ensure that the inserted default will be greater than getdate(). And suddenly a row is returned.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
In place of
WHERE (data.tblBooking.DateD >= GETDATE())
use
WHERE (DATEDIFF(d, GETDATE(), data.tblBooking.DateD) > 0)
No conversions, no substrings, works with or without non-midnight time portion.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply