Access NOW() Equivalent?

[Evan Wade]

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()));

[Francois]

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()));
>

[Stacey]

"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.

[Michael Cole]

"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

[George Verras]

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()));
>