general function to insert a DATE correctly

  • Thread starter Thread starter pamelafluente
  • Start date Start date
P

pamelafluente

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
 
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
Click to expand...
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.
Click to expand...

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 said:
Dates are always a PITB...having said that, my experience has shown
that the format dd-MMM-yyyy will rarely lead you astray.
Click to expand...

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



[email protected] said:
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.
Click to expand...

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
Click to expand...
 
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.
Click to expand...

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
 
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')
Click to expand...

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 )
Click to expand...

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 ..
 
BTW I did not know that ACCESS also takes "'" as delimiter.
I think I always read one has to use "#" (?)
Click to expand...

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 ha scritto:
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.
Click to expand...

That's a detail very nice to know. Thanks! :)
 
Pamela,

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

at least show this
as
Click to expand...

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


(O)enone ha scritto:
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.
Click to expand...

That's a detail very nice to know. Thanks! :)
Click to expand...
 
Cor said:
Pamela,

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

at least show this
Click to expand...

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).
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.
Click to expand...

I'm not sure what you mean by this, I thought we'd cleared up date handling
question fairly well personally.
 
I'm not sure what you mean by this, I thought we'd cleared up date
handling
question fairly well personally.
Click to expand...
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
 
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
 
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
 
Oenone said:
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.
Click to expand...

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, (e-mail address removed)

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 
Erland Sommarskog ha scritto:
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.
Click to expand...

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
 
What are you asking? How to add these columns to the database table? Can you
be more specific.
 
Back
Top