DATEADD Skullduggery. 1

[travisbrown]

Why would this be returning year 5157?

SELECT DateAdd('m',Date(),1)

07/31/5157

But this

SELECT DateAdd('d',Date(),1)

returns

01/10/2007?

I need to return the year of the date in one month, so I want to do something like this:
DATEPART('yyyy',DATEADD('m',DATE(),1))

 

[PHV]

And what about this ?
DateAdd('m',1,Date())

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

As PHV has shown ... you have the arguments in the wrong order. DateAdd is

DateAdd (Interval, Number, DateTimeStamp)

For Example DateAdd ("d", 100, Date())

 

[travisbrown]

Yes, thanks both. In any other world other than Access, that'd have given me an error message.

 

[Golom]

Well ... it isn't an "error" because a DateTime stamp is a number and a numeric in the third field is interpreted as the date that is that number of days after Dec 30, 1899, so

DateAdd ("d", Date(), 1)

which, if run today (Jan 10, 2007), translates to

DateAdd ("d", 39092, #12/31/1899#)

and that results in 01/11/2007 (i.e. Today plus 1 day)

 

[travisbrown]

Yeah, i suppose that's right. Like running CDBL on it.

 

[travisbrown]

And I guess I was doubly foiled because this returned the right result.

SELECT DateAdd('d',Date(),1)

returns 

01/10/2007

 

[Golom]

True.

DateAdd ( "d", Date(), n)

and 

DateAdd ( "d", n, Date())

Where 'n' is any number, return the same thing because of the way dates are numbered and the DateAdd arguments are interpreted.

PS. Actually DateAdd("d",Date(),1) returns 01/11/2007 if Date() is 01/10/2007.

 

[travisbrown]

Yeah, but I originally posted on the 9th