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