Access NOW() Equivalent?


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: Access NOW() Equivalent?

  1. #1
    Evan Wade Guest

    Access NOW() Equivalent?


    Is there a SQL server equivalent to the NOW() function in MS-Access? I haven't
    had any success using the GETDATE() function in its place. Here's the Access
    version of the query I'm trying to convert:

    SELECT News.Headline, News.Article, News.DebutDate, News.ExpiryDate, News.LinkText,
    News.LinkURL FROM News WHERE (((News.DebutDate)<=Now()) AND ((News.ExpiryDate)>=Now()));


  2. #2
    Francois Guest

    Re: Access NOW() Equivalent?


    Hi,

    Sometimes comparing date values in TSQL gives unsatisfactory results if you
    come from the Access world. Some that we have used successfully is to convert
    GETDATE() and the date you are comparing to to nvarchar values and then doing
    the comparison, like so:

    WHERE CONVERT(nvarchar, News.DebutDate, 112) <= CONVERT(nvarchar, GETDATE(),
    112).

    112 seems to be the best supported date format if you are dealing with multiple
    international date formats.

    "Evan Wade" <ewade@vusd.k12.ca.us> wrote:
    >
    >Is there a SQL server equivalent to the NOW() function in MS-Access? I

    haven't
    >had any success using the GETDATE() function in its place. Here's the Access
    >version of the query I'm trying to convert:
    >
    >SELECT News.Headline, News.Article, News.DebutDate, News.ExpiryDate, News.LinkText,
    >News.LinkURL FROM News WHERE (((News.DebutDate)<=Now()) AND ((News.ExpiryDate)>=Now()));
    >



  3. #3
    Stacey Guest

    Re: Access NOW() Equivalent?


    "Evan Wade" <ewade@vusd.k12.ca.us> wrote:

    < SNIP>

    >SELECT News.Headline, News.Article, News.DebutDate, News.ExpiryDate, News.LinkText,
    >News.LinkURL FROM News WHERE (((News.DebutDate)<=Now()) AND ((News.ExpiryDate)>=Now()));


    Another possibility is to use the DateDiff function (really useful for stuff
    like this).

    In this example I'm assuming you want hits where DebutDate is prior to the
    current day and the ExpiryDate is after the current day...

    WHERE (DateDiff (day, News.DebutDate , GetDate() ) > 0) AND (DateDiff (day,
    GetDate() , News.ExpiryDate ) > 0)

    The actual syntax is: DATEDIFF(datepart, startdate, enddate) from Books
    Online. Hope this helps.

    ---------------------------------------------------------------------------
    Stacey Hopkins
    Sr. Analyst
    Collateral and Document Collection

    This posting is provided "AS IS" with no warranties. You assume all risk
    for your use. Neither myself nor my employer is responsible for any damages
    you incur from this posting.


  4. #4
    Michael Cole Guest

    Re: Access NOW() Equivalent?


    "Evan Wade" <ewade@vusd.k12.ca.us> wrote in message
    news:3bd08b12$1@news.devx.com...
    >
    > Is there a SQL server equivalent to the NOW() function in MS-Access? I

    haven't
    > had any success using the GETDATE() function in its place.


    current_timestamp




  5. #5
    George Verras Guest

    Re: Access NOW() Equivalent?


    Evan,
    Try this...
    Create a variable and populate it with getdate()

    Declare @now datetime

    select @now = getdate()
    -- then do your query...

    SELECT News.Headline, News.Article, News.DebutDate, News.ExpiryDate, News.LinkText,
    News.LinkURL FROM News WHERE (((News.DebutDate)<= @now ) AND ((News.ExpiryDate)>=
    @now ));



    "Evan Wade" <ewade@vusd.k12.ca.us> wrote:
    >
    >Is there a SQL server equivalent to the NOW() function in MS-Access? I

    haven't
    >had any success using the GETDATE() function in its place. Here's the Access
    >version of the query I'm trying to convert:
    >
    >SELECT News.Headline, News.Article, News.DebutDate, News.ExpiryDate, News.LinkText,
    >News.LinkURL FROM News WHERE (((News.DebutDate)<=Now()) AND ((News.ExpiryDate)>=Now()));
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center