Professional Web Applications Themes

Date Parameter For Saved Parameter Queries - ASP Database

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

  1. #1

    Default Date Parameter For Saved Parameter Queries

    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

    melody Guest

  2. #2

    Default Re: Date Parameter For Saved Parameter Queries

    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 Guest

  3. #3

    Default Re: Date Parameter For Saved Parameter Queries

    > 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]


    Aaron Bertrand [MVP] Guest

  4. #4

    Default Re: Date Parameter For Saved Parameter Queries


    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]
    >
    >
    >.
    >
    Guest

  5. #5

    Default Re: Date Parameter For Saved Parameter Queries


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

  6. #6

    Default Re: Date Parameter For Saved Parameter Queries

    > 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]


    Aaron Bertrand [MVP] Guest

  7. #7

    Default Re: Date Parameter For Saved Parameter Queries

    [email]anonymousdiscussions.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"


    Bob Barrows Guest

  8. #8

    Default Re: Date Parameter For Saved Parameter Queries

    > 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]


    Aaron Bertrand - MVP Guest

  9. #9

    Default Re: Date Parameter For Saved Parameter Queries

    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" <reb01501NOyahoo.SPAMcom> wrote in message
    news:urF3ROxvDHA.556TK2MSFTNGP11.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 Guest

  10. #10

    Default Re: Date Parameter For Saved Parameter Queries

    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.


    Bob Barrows Guest

  11. #11

    Default Re: Date Parameter For Saved Parameter Queries

    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 Guest

  12. #12

    Default Re: Date Parameter For Saved Parameter Queries

    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-----
    >anonymousdiscussions.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"
    >
    >
    >.
    >
    melody Guest

  13. #13

    Default Re: Date Parameter For Saved Parameter Queries

    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"


    Bob Barrows Guest

  14. #14

    Default Re: Date Parameter For Saved Parameter Queries

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

Similar Threads

  1. Replies: 0
    Last Post: December 4th, 12:46 AM
  2. #38178 [Asn]: Problem with parameter detection in queries (HY093)
    By gerald at copix dot org in forum PHP Bugs
    Replies: 0
    Last Post: September 21st, 02:25 PM
  3. Access date parameter problem
    By AHT Barry in forum Coldfusion Database Access
    Replies: 2
    Last Post: June 27th, 11:34 PM
  4. Another saved parameter query question (MSAccess XP)
    By Larry Rekow in forum ASP Database
    Replies: 2
    Last Post: August 5th, 02:47 AM
  5. MS Access d/b saved parameter query problem
    By Larry Rekow in forum ASP Database
    Replies: 3
    Last Post: August 3rd, 07:38 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139