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

re: general function to insert a DATE correctly

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

re: general function to insert a DATE correctly

<pamelafluente@libero.itwrote in message
news:1167812246.168644.119680@n51g2000cwc.googlegr oups.com...Dates are always a PITB...having said that, my experience has shown that the
format dd-MMM-yyyy will rarely lead you astray.

re: general function to insert a DATE correctly

Harry Strybos ha scritto:
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

re: general function to insert a DATE correctly

Harry Strybos wrote: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

re: general function to insert a DATE correctly

Oenone ha scritto:
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

re: general function to insert a DATE correctly

Michel Posseth [ MCP ] ha scritto:
Thanks a lot. Very helpful. I will go definitely this way then :))


-P

re: general function to insert a DATE correctly

Michel ha scritto:
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 ..

re: general function to insert a DATE correctly

pamelafluente@libero.it wrote: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

re: general function to insert a DATE correctly

(O)enone ha scritto:
That's a detail very nice to know. Thanks! :)

re: general function to insert a DATE correctly

Pamela,

In my opinion are you making a very confusing post for others.

at least show thisas
You ask here the same if we can find a solution which uses a bit field to
enter all the names of all Italians.

For the DateTime is a proper solution, if you cannot use that, ok but don't
confuse people in future who want to do it in the right way.

Just my opinion.

Cor


<pamelafluente@libero.itschreef in bericht
news:1167830046.855885.314870@k21g2000cwa.googlegr oups.com...

re: general function to insert a DATE correctly

Cor Ligthert [MVP] wrote:That was my SQL actually, not Pamela's. "DateField" is the correct name for
the field as it actually is a date field, not a string field. The string
representation of the date is being used purely within the SELECT statement
(inside which it has to be represented as a string, as SQL is written using
strings).
I'm not sure what you mean by this, I thought we'd cleared up date handling
question fairly well personally.

--

(O)enone

re: general function to insert a DATE correctly

And I think that all given solutions are not anymore from this century.

A DateTime is handled normally by a DateTime not by a String.

There is in an Access Server no year month day hour or whatever or it should
be placed there as a string or parts of other datafields.

A proper datafields exist from ticks starting somewhere in january 1783
counted in 100/3 milliseconds.

Cor

re: general function to insert a DATE correctly

Cor,

I guess you mist the folllow up posting of pamela wich explains why she
choosed for the string aproach

<<<<
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.Otherwise ofcourse a datetime value aproach would have made more sence

however in this situation you have a problem as you should have to determine
in wich format the date value was originaly stored or expected

If you choose for the ISO 8601 string aproach ( wich is the recomended parse
way acording to the MSDN documentation ) you do not have this problem,
however it is only intended for inserts , as the DB converts the value to a
datetime value you should query on the data with the apropriate data type

regards

Michel




"Cor Ligthert [MVP]" wrote:

re: general function to insert a DATE correctly

Michel,

That was what I said in my previous message, it is the best approach there
where you use strings.

That I wrote specially for people searching for solutions.

However O(enone) find the solution in this thread the alltime best because
it is so well discussed.

I thought that our opinion is the same..

Cor



"Michel Posseth [MCP]" <MichelPossethMCP@discussions.microsoft.comschre ef
in bericht news:A4A6BB25-C3FC-44AF-960A-EF27B7769E67@microsoft.com...

re: general function to insert a DATE correctly

Oenone (oenone@nowhere.com) writes:Unfortunately, YYYY-MM-DD HH:mm:ss is not a safe format on SQL Server,
but will fail if the dateformat is DMY (which it typically would be on
an Italian server.) YYYYMMDD is a safe format, and so is YYYY-MM-DDTHH:mm:ss
(on SQL 2000 and later) and YYYY-MM-DDZ (on SQL 2005 only). T and Z here
stand for themselves.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

re: general function to insert a DATE correctly

Erland Sommarskog ha scritto:
Thanks Erland ,

following your suggestion and the preceding, I will use then:

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

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

which are meant to be used on target fields of type DateTime (let me
know if you foresee possible problems or improvements)

-P

re: general function to insert a DATE correctly

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: