Date Parameter when using Intl Date Formats

[Lauren Quantrell]

I already figured out (the hard way) I need to convert all my date
parameters into USA format before executing my stored procedures where
dates are used as parameters.
(Format(StartDate, "m/d/yyyy hh:nn:ss AM/PM")

At least I thought I did!

But then I discover that if I use the following construction, the dates
do not need to be formatted to USA first:

Dim StartDate as Date, EndDate as Date

StartDate = Date()
EndDate = Date() +1

cmd.Execute , Parameters:=Array(StartDate, EndDate),
Options:=adExecuteNoRecords

Am I smoking dope here or did our MS friends make this more confusing
than necessary?

[RoyVidar]

Lauren Quantrell wrote in message
<11*********************@o13g2000cwo.googlegroups. com> :

I already figured out (the hard way) I need to convert all my date
parameters into USA format before executing my stored procedures where
dates are used as parameters.
(Format(StartDate, "m/d/yyyy hh:nn:ss AM/PM")

At least I thought I did!

But then I discover that if I use the following construction, the dates
do not need to be formatted to USA first:

Dim StartDate as Date, EndDate as Date

StartDate = Date()
EndDate = Date() +1

cmd.Execute , Parameters:=Array(StartDate, EndDate),
Options:=adExecuteNoRecords

Am I smoking dope here or did our MS friends make this more confusing
than necessary?

There's a bit of difference between concatenating some dynamic
sql STRING, where you'll need to spoonfeed it with enough
information to make correct assessement of the passed STRING,
and passing parameters of a specific/specified datatype.

The latter, is perhaps comparable to assigning values between
variables of the same datatype

somedate = somotherdate

Those methods makes it a bit easier to deal with single quotes
for text parametes and other special characters, too.

I'll more often do something like

with cmd
...
.set prm = .createparameter("MyDate", addate, adparaminput)
.parameters.append prm
prm.value = dtMyDate
...

--
Roy-Vidar

[Lauren Quantrell]

Roy,
Yes thanks. The problem seems to come when I store the date value in a
text field. That always has to be converted when refering to the text
fiield in a parameter:

.set prm = .createparameter("MyDate", addate, adparaminput)
.parameters.append prm
.prm.value = (Format(Me.StartDate, "m/d/yyyy hh:nn:ss AM/PM")

And also when using the InputParameters property of a property sheet:

@StartDate = Date()

does not work but:

Format(Date(), "m/d/yyyy hh:nn:ss AM/PM")

does work.

That I find strange!

[Lyle Fairfield]

Lauren Quantrell wrote:

yyyy-mm-dd hh:nn:ss

always works

It has the added advantage of being the accepted notation in the
civilized (civilized=non Anglo) world.

[RoyVidar]

Lauren Quantrell wrote in message
<11**********************@z14g2000cwz.googlegroups .com> :

Roy,
Yes thanks. The problem seems to come when I store the date value in a
text field. That always has to be converted when refering to the text
fiield in a parameter:

.set prm = .createparameter("MyDate", addate, adparaminput)
.parameters.append prm
.prm.value = (Format(Me.StartDate, "m/d/yyyy hh:nn:ss AM/PM")

And also when using the InputParameters property of a property sheet:

@StartDate = Date()

does not work but:

Format(Date(), "m/d/yyyy hh:nn:ss AM/PM")

does work.

That I find strange!

I don't know about the other things, but here

.prm.value = (Format(Me.StartDate, "m/d/yyyy hh:nn:ss AM/PM")

you are effectively doing something like this
- take the contents of (presumably) a text control, which might
be a valid date
- format it to a STRING representation of that date in US date
- this is coerced/cast implicitly back to being a date when
assigned to the parameter

Doing stuff with dates seems to bring a lot of interresting
challenges. What I try to be carefull about, is keeping
dates as dates, and try to avoid formatting dates to strings
and back when assigning. When living in nondefault places,
thats often where the amusement starts;-)

I think I'd validate the contents of the text control, assign
to a date variable, perhaps, or directly to the parameter
without formatting between date and string.

If you wan't to use format, I think you'd need to
disambiguate it, use for instance the one Allen
Browne shows here
http://allenbrowne.com/ser-36.html
"mm\/dd\/yyyy ... or the one Lyle Fairfield presents,
elsethreads, but darned if I know what happens if you
let Access implicitly cast a string representation of an
US date to a variable of datatype date, when the system
on which it is run has it's "belief system" anchored to
UK regional settings, but I think it will create some
hillarius hours of debugging when both the day and
month are less than 13;-)

' try this with UK settings ...

dim dt1 as date
dim dt2 as date
dim dt3 as date
dt1 = #10/12/2005#
dt2 = fomat$(dt1, "m/d/yyyy")
dt3 = dt1
debug.print dt1, dt2, dt3

--
Roy-Vidar

[Lauren Quantrell]

I'll always use global variables in any future projects to hold date
values rather than populating text fields on forms. That's after many
days and hours of debugging. Never again!
What I can't figure out is why, in a form's property sheet/input
parameters, when set to int'l time formats:
@StartDate = Date() does not work but @StartDate = Format(Date(),
"m/d/yyyy hh:nn:ss AM/PM") does

[Lyle Fairfield]

Are you using an ADP?
Is this MS-SQL?

[Lauren Quantrell]

Yes MS-SQL 2000, Access 2000 ADP/ADE

[Lauren Quantrell]

I think I have decided in future projects the following scheme would be
better overall:

In the front end app take the desired date and do a DATEDIFF with the
current date, then pass the date diff as a parameter as an integer
rather than deal with the dates at all. In this way, the stored
procedure would use parameters like @StartDays int, @EndDays int
instead of @StartDate datetime, @EndDate datetime. Then use GETDATE()
in the stored procedure and DATEADD to rebuild the dates.

As an example, if I want to look at something where tomorrow's date
would have been the parmaeter I'd just pass 1 instead of date()+1. For
a date range of yesterday through two days from now, I'd pass
parameters of -1 and 2 instead of date()-1 and date()+2. I think
overall, this would simplify the whole process - never using date
parameters.

[Lyle Fairfield]

Well, I should have noticed that you said Stored Procedures in your
original post.

TTBOMR input parameters are strings.

@StartDate = Date() might not produce a great string while @StartDate
= Format(Date(),
"m/d/yyyy hh:nn:ss AM/PM") does.

When working with ADPs and MS-SQL one often has to deal with the fact
that Access and JET are inter-related, while Access and MS-SQL have
nothing to do with each other.

Input parameters are one of the really obtuse features of ADP, as you
seem to be finding out. (I've found them to be so ugly that I've forced
them from my mind, which is why I said, TTBOMR").

If you do a google groups search on this topic you will gind many tales
of woe and much gnanching of teeth.

[Lyle Fairfield]

I wrote one reply and removed it.
Input Parameters have been, for me, very gnarly things.
I have been most successful when remembering that they must be strings
which MS-SQL can understand.
So what will
@StartDate = Date()
return? I don't know. On my computer it returns
@StartDate = 2005-11-28
MS-SQL (or Access) doesn't know that I want it to convert 2005-11-28 to
a date. But it will handle
'2005-11-28'
and
#2005-11-28#
and I expect it will also handle the dates expressed as
#mm/dd/yyyy#

[Terry Kreft]

The easiest way to handle passing dates to SQL Server is to pass it in one
of the formats which Convert understands, then immediately convert it to a
datetime variable.

So for instance you could pass a date in international format.

Format(Date, "YYYYMMDD")

and then in your SP

Create Procedure usp_some_proc
@indate varchar(10)
AS
Declare @real_date datetime

Select @real_date = Convert(datetime, @indate, 112)
-- Then you can just work with @real_date and not worry about the format


--
Terry Kreft

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

I wrote one reply and removed it.
Input Parameters have been, for me, very gnarly things.
I have been most successful when remembering that they must be strings
which MS-SQL can understand.
So what will
@StartDate = Date()
return? I don't know. On my computer it returns
@StartDate = 2005-11-28
MS-SQL (or Access) doesn't know that I want it to convert 2005-11-28 to
a date. But it will handle
'2005-11-28'
and
#2005-11-28#
and I expect it will also handle the dates expressed as
#mm/dd/yyyy#