general function to insert a DATE correctly

Hi I have to insert dates into some Access and SQL Databases.

I need to be general as the target computer might be in any country.

--------
- For access I wrote the follow:

Function Date_ACCESS(ByVal Data As Date) As String
Return "#" & Data.Year & "-" & Data.Month & "-" & Data.Day & "
" & _
Data.Hour & ":" & Data.Minute & ":" & Data.Second
& "#"
End Function

I wish to know if this is correct or it might fail for some setting
(eg. 12 / 24 hours)
or if there is a better way to write this function. Thanks


---------
- For SQL server I am a little confused. I would like your help to
write a general function which
yields the correct date whatever is the setting of target DBMS
(possibly, some culture related info migh be a parameter, if needed).

Function Date_SQLServer(ByVal Data As Date, CultureInfo as ... ) As
String
'...
End Function

Please help. Thanks


-Pam

A one more information to avoid wasting your time. I need the string
and I cannot use parameters because this is used to create a dump text
file with INSERT commands.

Thanks again.

-P

<pamelafluente@libero.it> wrote in message
news:1167812246.168644.119680@n51g2000cwc.googlegroups.com...
>
> Hi I have to insert dates into some Access and SQL Databases.
>
> I need to be general as the target computer might be in any country.
>
> --------
> - For access I wrote the follow:
>
> Function Date_ACCESS(ByVal Data As Date) As String
> Return "#" & Data.Year & "-" & Data.Month & "-" & Data.Day & "
> " & _
> Data.Hour & ":" & Data.Minute & ":" & Data.Second
> & "#"
> End Function
>
> I wish to know if this is correct or it might fail for some setting
> (eg. 12 / 24 hours)
> or if there is a better way to write this function. Thanks
>
>
> ---------
> - For SQL server I am a little confused. I would like your help to
> write a general function which
> yields the correct date whatever is the setting of target DBMS
> (possibly, some culture related info migh be a parameter, if needed).
>
> Function Date_SQLServer(ByVal Data As Date, CultureInfo as ... ) As
> String
> '...
> End Function
>
> Please help. Thanks
>
>
> -Pam
>
Dates are always a PITB...having said that, my experience has shown that the
format dd-MMM-yyyy will rarely lead you astray.

Harry Strybos ha scritto:

> Dates are always a PITB...having said that, my experience has shown that the
> format dd-MMM-yyyy will rarely lead you astray.

According to Michel ...
It seems I got right at least the ACCESS way (I hope).

[ dd-MMM-yyyy would fail on Access in my Italian mdb, for instance
(comes out a wrong datetime) ]

Actually the big headache is with SQL server (and similar systems) I am
not sure how I can write to function in order to be general. Should I
convert to string a known date to determine the actual setting and the
create the target string accordingly ?

Who is able to help me finding a final solution ??

These date are really a PITB !

-P

Harry Strybos wrote:
> Dates are always a PITB...having said that, my experience has shown
> that the format dd-MMM-yyyy will rarely lead you astray.

The problem with that format is that the month part may not be interpreted
if it is sent to/from a piece of software running in a different language.
For example, a date in April generated on a French system would read as
"01-Avr-2006". If you feed this into an English system, it won't be able to
interpret "Avr" as a valid month.

The only way to reliably represent dates as strings is to use ISO8601
format. For dates, this is "yyyy-MM-dd", for date/times it is "yyyy-MM-dd
HH:mm:ss". This is identifiable and unambiguous. All the database engines
I've tested this format with (SQL Server, Access, MySQL) have interpreted
this correctly. And also it very conveniently sorts into the correct order
when an alphabetical sort is applied to the string (unlike virtually every
other date format). Dates should always always be stored in this format when
a string representation is required (IMO).

Personally I wish the world would adopt yyyy-MM-dd for all written dates (on
computer or on paper) instead of the inconsistent and sometimes ridiculous
systems we use at the moment, but I can't see it happening somehow.

--

(O)enone

According to the SQL documentation

In Microsoft SQL Server 2005, you can specify date and time data by using
the ISO 8601 format.

This is the format:

yyyy-mm-ddThh:mm:ss[.mmm]

he brackets indicate that the fraction of seconds component is optional. The
time component is specified in the 24-hour format.



The advantage in using the ISO 8601 format is that it is an international
standard. Also, datetime values that are specified by using this format are
unambiguous. Also, this format is not affected by the SET DATEFORMAT or SET
LANGUAGE settings.

Following are two examples of datetime values that are specified in the ISO
8601 format:

2004-05-23T14:25:10
2004-05-23T14:25:10.487



HTH

Michel



"pamelafluente@libero.it" wrote:

> Harry Strybos ha scritto:
>
> > Dates are always a PITB...having said that, my experience has shown that the
> > format dd-MMM-yyyy will rarely lead you astray.
>
> According to Michel ...
> It seems I got right at least the ACCESS way (I hope).
>
> [ dd-MMM-yyyy would fail on Access in my Italian mdb, for instance
> (comes out a wrong datetime) ]
>
> Actually the big headache is with SQL server (and similar systems) I am
> not sure how I can write to function in order to be general. Should I
> convert to string a known date to determine the actual setting and the
> create the target string accordingly ?
>
> Who is able to help me finding a final solution ??
>
> These date are really a PITB !
>
> -P
>
>

Oenone ha scritto:

>
> Personally I wish the world would adopt yyyy-MM-dd for all written dates (on
> computer or on paper) instead of the inconsistent and sometimes ridiculous
> systems we use at the moment, but I can't see it happening somehow.

Let me get this right.

Are you saying that I could just use the same function I am using for
ACCESS just replacing the delimiter "#" with the quotes "'" or """" ?

Is this what you are implying? My doubt is how would SQL server know
that I am using ISO8601 notation ? I am missing just this part.

-P

>
> --
>
> (O)enone

Michel Posseth [ MCP ] ha scritto:

> Just to be sure i have just tested this for you in Access ( 2003 sp2 )
>
> INSERT INTO Testtabel
> (Test)
> Values ('2007-01-01')
>
>

Thanks a lot. Very helpful. I will go definitely this way then )


-P

Michel ha scritto:

> Just to be sure i have just tested this for you in Access ( 2003 sp2 )

mmm...

BTW I did not know that ACCESS also takes "'" as delimiter.
I think I always read one has to use "#" (?)

perhaps they are standardizing this ..

>
> INSERT INTO Testtabel
> (Test)
> Values ('2007-01-01')

pamelafluente@libero.it wrote:
> BTW I did not know that ACCESS also takes "'" as delimiter.
> I think I always read one has to use "#" (?)

I just tested this in Access 2000.

For the INSERT statement that Michel posted, Access works fine and correctly
inserts the date without problem.

However if you use a SELECT statement:

\\\
SELECT *
FROM Table1
WHERE DateField = '2007-01-01'
///

....this displays a data type mismatch error and refuses to run. Replacing
the quotes with hash characters makes it run properly.

HTH,

--

(O)enone