By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,870 Members | 1,224 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,870 IT Pros & Developers. It's quick & easy.

general function to insert a DATE correctly

P: n/a

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

Jan 3 '07 #1
Share this Question
Share on Google+
17 Replies


P: n/a
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

Jan 3 '07 #2

P: n/a
<pa***********@libero.itwrote in message
news:11**********************@n51g2000cwc.googlegr oups.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.
Jan 3 '07 #3

P: n/a
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

Jan 3 '07 #4

P: n/a
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

Jan 3 '07 #5

P: n/a
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
Jan 3 '07 #6

P: n/a

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

Jan 3 '07 #7

P: n/a

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')
Jan 3 '07 #8

P: n/a
pa***********@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
Jan 3 '07 #9

P: n/a

(O)enone ha scritto:
pa***********@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.
That's a detail very nice to know. Thanks! :)
>
HTH,

--

(O)enone
Jan 3 '07 #10

P: n/a
Pamela,

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

at least show this
>\\\
SELECT *
FROM Table1
WHERE DateField = '2007-01-01'
///
as
>\\\
SELECT *
FROM Table1
WHERE StringDateField = '2007-01-01'
///
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
<pa***********@libero.itschreef in bericht
news:11**********************@k21g2000cwa.googlegr oups.com...
>
(O)enone ha scritto:
>pa***********@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.

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

--

(O)enone

Jan 3 '07 #11

P: n/a
Cor Ligthert [MVP] wrote:
Pamela,

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

at least show this
>>\\\
SELECT *
FROM Table1
WHERE DateField = '2007-01-01'
///

as
>>\\\
SELECT *
FROM Table1
WHERE StringDateField = '2007-01-01'
///
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.
I'm not sure what you mean by this, I thought we'd cleared up date handling
question fairly well personally.

--

(O)enone
Jan 3 '07 #12

P: n/a
I'm not sure what you mean by this, I thought we'd cleared up date
handling
question fairly well personally.
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
Jan 4 '07 #13

P: n/a
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:
>
I'm not sure what you mean by this, I thought we'd cleared up date
handling
question fairly well personally.
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
Jan 4 '07 #14

P: n/a
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]" <Mi**************@discussions.microsoft.comschre ef
in bericht news:A4**********************************@microsof t.com...
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:
>>
I'm not sure what you mean by this, I thought we'd cleared up date
handling
question fairly well personally.
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

Jan 4 '07 #15

P: n/a
Oenone (oe****@nowhere.com) writes:
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.
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, es****@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
Jan 4 '07 #16

P: n/a

Erland Sommarskog ha scritto:
Oenone (oe****@nowhere.com) writes:
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.

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

--
Erland Sommarskog, SQL Server MVP, es****@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
Jan 7 '07 #17

P: n/a
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

"pa***********@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

Jan 10 '07 #18

This discussion thread is closed

Replies have been disabled for this discussion.