Date Parameter For Saved Parameter Queries

[melody]

Hi again,

I finally got to using saved parameter queries in my
application (a big thank you to Bob Barrows for helping me
with this). Currently I need to pass dates as a parameter
to my saved query in Access. My question is : what date
format should be used for the parameter ?

Just some additional information :
The users of my application use JavaScript calendars to
pick dates and the date format is "mm/dd/yyyy". And in
Access, I defined the relevant parameters for my saved
query as date/time datatypes.

Really appreciate any help and advice.

Thanks in advance,
melody

[Bob Barrows]

melody wrote:

> Hi again,
>
> I finally got to using saved parameter queries in my
> application (a big thank you to Bob Barrows for helping me
> with this). Currently I need to pass dates as a parameter
> to my saved query in Access. My question is : what date
> format should be used for the parameter ?
>
> Just some additional information :
> The users of my application use JavaScript calendars to
> pick dates and the date format is "mm/dd/yyyy". And in
> Access, I defined the relevant parameters for my saved
> query as date/time datatypes.
>
> Really appreciate any help and advice.
>
> Thanks in advance,
> melody

Use a date variable. If you assign a date to a date variable (using CDate or
DateSerial), it will pass it as a date.

dim dDate
dDate = DateSerial(2003,12,10)
....
cn.savedquery dDate

HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Aaron Bertrand [MVP]]

> to my saved query in Access. My question is : what date

> format should be used for the parameter ?

As a string, the safest format is "YYYY-MM-DD" ... mm/dd/yyyy is ambiguous
and could lead to incorrect data, or errors, if your environment changes.

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

Thanks a lot for the advice. May you elaborate a little on
why "mm/dd/yyyy" is ambiguous ?

Melody

>-----Original Message-----

>> to my saved query in Access. My question is : what date
>> format should be used for the parameter ?

>
>As a string, the safest format is "YYYY-MM-DD" ...

mm/dd/yyyy is ambiguous

>and could lead to incorrect data, or errors, if your

environment changes.

>
>--
>Aaron Bertrand
>SQL Server MVP
>[url]http://www.aspfaq.com/[/url]
>
>
>.
>

[melody]

Thanks again for your help :-)

Melody

>-----Original Message-----
>melody wrote:

>> Hi again,
>>
>> I finally got to using saved parameter queries in my
>> application (a big thank you to Bob Barrows for helping

me

>> with this). Currently I need to pass dates as a

parameter

>> to my saved query in Access. My question is : what date
>> format should be used for the parameter ?
>>
>> Just some additional information :
>> The users of my application use JavaScript calendars to
>> pick dates and the date format is "mm/dd/yyyy". And in
>> Access, I defined the relevant parameters for my saved
>> query as date/time datatypes.
>>
>> Really appreciate any help and advice.
>>
>> Thanks in advance,
>> melody

>
>Use a date variable. If you assign a date to a date

variable (using CDate or

>DateSerial), it will pass it as a date.
>
>dim dDate
>dDate = DateSerial(2003,12,10)
>....
>cn.savedquery dDate
>
>HTH,
>Bob Barrows
>
>--
>Microsoft MVP - ASP/ASP.NET
>Please reply to the newsgroup. This email account is my

spam trap so I

>don't check it very often. If you must reply off-line,

then remove the

>"NO SPAM"
>
>
>.
>

[Aaron Bertrand [MVP]]

> Thanks a lot for the advice. May you elaborate a little on

> why "mm/dd/yyyy" is ambiguous ?

Sure. What month is 06/08/2003? Your answer will depend on whether you are
in Canada, England, Australia, Japan, Germany, the US, etc. And also if you
are in the US and know that, for whatever reason, your system is set up with
UK dateformat, or vice versa. So if you're wrong, you either get bogus data
in your database (e.g. 06/08/2003 = August 6th instead of June 8th), or you
get errors (e.g. 06/15/2003 fails because it can't figure out what month is
the 15th month). Yes, Access can help you avoid this latter error because
it will transpose month and day if one of them is too big (regardless of
format). But most sane database systems will actually let you know about
the format error instead of just assuming what you meant and storing bogus
data.

Even if you have complete and utter confidence that all components in your
system are set up to accept one format, and that nobody can possibly change
that, always use YYYYMMDD for SQL Server and YYYY-MM-DD for Access. Then
neither humans nor software can be confused and accidentally transpose month
and day.

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[Bob Barrows]

[email]anonymous@discussions.microsoft.com[/email] wrote:

> Thanks a lot for the advice. May you elaborate a little on
> why "mm/dd/yyyy" is ambiguous ?
>

"mm/dd/yyyy" is ambiguous because a large part of the world uses
"dd/mm/yyyy" to express dates. So to some parts of the world, including the
US, 2/1/2003 represents Feb. 1, and to other parts of the world, it
represents Jan. 2.

The ISO format, yyyymmdd, would be equally ambiguous if somewhere in the
world, somebody was using "yyyyddmm" to express dates. AFAIK, nobody is.
That makes yyyymmdd less ambiguous than mm/dd/yyyy. 20030201 will always
represent Feb. 1.

HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Aaron Bertrand - MVP]

> The ISO format, yyyymmdd, would be equally ambiguous if somewhere in the

> world, somebody was using "yyyyddmm" to express dates. AFAIK, nobody is.

And more importantly, no software will confuse YYYYMMDD for YYYYDDMM, unless
someone explicitly wrote it to interpret dates that way. This is why
YYYYMMDD and YYYY-MM-DD are international standards, and m/d/yyyy or
d/m/yyyy are just "the way a country does it."

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[Bob]

What if there is a start date and end date parameter?
Would the format them be:

dim dStartDate
dim dEndDate
dStartDate = DateSerial(2003,12,10)
dEndDate = DateSerial(2003,12,12)
...
cn.savedquery dStartDate dEndDate

or should there be some kind of separator between the 2 parameters?

Thanks.

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:urF3ROxvDHA.556@TK2MSFTNGP11.phx.gbl...

> melody wrote:

> > Hi again,
> >
> > I finally got to using saved parameter queries in my
> > application (a big thank you to Bob Barrows for helping me
> > with this). Currently I need to pass dates as a parameter
> > to my saved query in Access. My question is : what date
> > format should be used for the parameter ?
> >
> > Just some additional information :
> > The users of my application use JavaScript calendars to
> > pick dates and the date format is "mm/dd/yyyy". And in
> > Access, I defined the relevant parameters for my saved
> > query as date/time datatypes.
> >
> > Really appreciate any help and advice.
> >
> > Thanks in advance,
> > melody

>
> Use a date variable. If you assign a date to a date variable (using CDate

or

> DateSerial), it will pass it as a date.
>
> dim dDate
> dDate = DateSerial(2003,12,10)
> ...
> cn.savedquery dDate
>
> HTH,
> Bob Barrows
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>

[Bob Barrows]

Bob wrote:

> What if there is a start date and end date parameter?
> Would the format them be:
>
> dim dStartDate
> dim dEndDate
> dStartDate = DateSerial(2003,12,10)
> dEndDate = DateSerial(2003,12,12)
> ...
> cn.savedquery dStartDate dEndDate
>
> or should there be some kind of separator between the 2 parameters?
>

Yes. The parameter arguments should be separated by commas, just as if you
were calling a vbs/ado method.

In addition, if the saved query recturns records, you can add an explicitly
instantiated recordset variable to the argument list to cause it to be
opened:

set rs=server.createobject("adodb.recordset")
cn.savedquery dStartDate, dEndDate, rs

'rs will now be opened and will contain the records returned by savedquery

HTH,
Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[melody]

Thank you for sharing :-)

>-----Original Message-----

>> Thanks a lot for the advice. May you elaborate a little

on

>> why "mm/dd/yyyy" is ambiguous ?

>
>Sure. What month is 06/08/2003? Your answer will depend

on whether you are

>in Canada, England, Australia, Japan, Germany, the US,

etc. And also if you

>are in the US and know that, for whatever reason, your

system is set up with

>UK dateformat, or vice versa. So if you're wrong, you

either get bogus data

>in your database (e.g. 06/08/2003 = August 6th instead of

June 8th), or you

>get errors (e.g. 06/15/2003 fails because it can't figure

out what month is

>the 15th month). Yes, Access can help you avoid this

latter error because

>it will transpose month and day if one of them is too

big (regardless of

>format). But most sane database systems will actually

let you know about

>the format error instead of just assuming what you meant

and storing bogus

>data.
>
>Even if you have complete and utter confidence that all

components in your

>system are set up to accept one format, and that nobody

can possibly change

>that, always use YYYYMMDD for SQL Server and YYYY-MM-DD

for Access. Then

>neither humans nor software can be confused and

accidentally transpose month

>and day.
>
>--
>Aaron Bertrand
>SQL Server MVP
>[url]http://www.aspfaq.com/[/url]
>
>
>.
>

[melody]

Thank you for the clarification :-) If I'm using
DateSerial and pass my parameter as a date, then
I don't need to worry about any ambiguity.. right ?

melody

>-----Original Message-----
>anonymous@discussions.microsoft.com wrote:

>> Thanks a lot for the advice. May you elaborate a little

on

>> why "mm/dd/yyyy" is ambiguous ?
>>

>"mm/dd/yyyy" is ambiguous because a large part of the

world uses

>"dd/mm/yyyy" to express dates. So to some parts of the

world, including the

>US, 2/1/2003 represents Feb. 1, and to other parts of the

world, it

>represents Jan. 2.
>
>The ISO format, yyyymmdd, would be equally ambiguous if

somewhere in the

>world, somebody was using "yyyyddmm" to express dates.

AFAIK, nobody is.

>That makes yyyymmdd less ambiguous than mm/dd/yyyy.

20030201 will always

>represent Feb. 1.
>
>HTH,
>Bob Barrows
>--
>Microsoft MVP - ASP/ASP.NET
>Please reply to the newsgroup. This email account is my

spam trap so I

>don't check it very often. If you must reply off-line,

then remove the

>"NO SPAM"
>
>
>.
>

[Bob Barrows]

melody wrote:

> Thank you for the clarification :-) If I'm using
> DateSerial and pass my parameter as a date, then
> I don't need to worry about any ambiguity.. right ?
>

right.



--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[melody]

Thanks :-)

Melody

>-----Original Message-----
>melody wrote:

>> Thank you for the clarification :-) If I'm using
>> DateSerial and pass my parameter as a date, then
>> I don't need to worry about any ambiguity.. right ?
>>

>right.
>
>
>
>--
>Microsoft MVP - ASP/ASP.NET
>Please reply to the newsgroup. This email account is my

spam trap so I

>don't check it very often. If you must reply off-line,

then remove the

>"NO SPAM"
>
>
>.
>