problem with now()

  • Thread starter Thread starter Tomas Andersson
  • Start date Start date
T

Tomas Andersson

I have a Sub with the following code
Public Sub Logg(ByVal Lvl, ByVal Designation, ByVal Uid, ByVal FileString,
ByVal ImportMode)
Dim AA As MySqlConnection = New MySqlConnection("server=" & MYSQLServer &
";database=" & MYSQLDB & ";uid=" & MYSQLUser & ";pwd=" & MYSQLPassword & "")
AA.Open()
Dim cmd As MySqlCommand = New MySqlCommand()
cmd.Connection = AA
cmd.CommandText = "INSERT INTO
spmon_log(DateNTime,Lvl,Designation,Uid,FileString,ImportMode) VALUES ('" &
Now().ToString & "','" & Lvl & "','" & Designation & "','" & Uid & "','" &
FileString & "','" & ImportMode & "') ON DUPLICATE KEY UPDATE
ID=LAST_INSERT_ID(ID)"
Dim DR As MySqlDataReader = cmd.ExecuteReader
DR.Close()
AA.Close()
AA = Nothing
End Sub
o
Trubble is that if I run this code in my worstation a correct datetime is
registered in the database 2009-04-17 08:21:22
But this code is a part of a service running on a Server 2003 R2 and the
result is 0000-00-00 00:00:00

What can i do to correct this?
 
Thomas,

The answer on this kind of questions especially when it is about dates is:
use sqlparameters.

You probably get a lot of messages about sql injections too, the latter is
in my idea mainly important with AspNet, especially as the parameters are
showed in the Get in the URL, by changing those you can then sometimes
completely change your sql transact.

The shortest sample I have about sqlParameters (with dates).

http://www.vb-tips.com/SQLParameters.aspx

Cor
 
Tomas Andersson said:
I have a Sub with the following code
[...]
cmd.CommandText = "INSERT INTO
spmon_log(DateNTime,Lvl,Designation,Uid,FileString,ImportMode) VALUES ('"
& Now().ToString & "','" & Lvl & "','" & Designation & "','" & Uid & "','"
& FileString & "','" & ImportMode & "') ON DUPLICATE KEY UPDATE
ID=LAST_INSERT_ID(ID)"
Trubble is that if I run this code in my worstation a correct datetime is
registered in the database 2009-04-17 08:21:22
But this code is a part of a service running on a Server 2003 R2 and the
Click to expand...

Try using this SQL syntax for date fields:
CONVERT(DATETIME, '--date in yyyy-MM-dd HH:mm:ss format here--', 102)

(NB: hour in in the range 0-24)

S.
 
Michel,

Can you explain why that is in your idea better to use an ISO 8601 date then
using sqlParameters, in my idea is a ISO 8601 date not working direct on a
US or for instance Dutch sqlServer.

For those who don't know what ISO 8601 is
http://en.wikipedia.org/wiki/ISO_8601

Cor
 
Hello Cor ,

SQL parameters would also do the job and is ofcourse favored , however if
the OP is sticking to concanated SQL then ISO 8601 would sure solve his
problem
in fact ISO 8601 will work on anny platform and localization , for instance
if you ever need to write date or date time to a text file ISO 8601 is the
way to go cause it will not fail .

ISO 8601 date not working direct on a US or for instance Dutch sqlServer.
Click to expand...

Well it did work when i tested it this is the format (
yyyy-mm-ddThh:mm:ss[.mmm] )

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.

HTH

Michel
 
Michel,

I know, in past I was always writing this, however I stopped with it as it
was often bad understood.

However, I still initialize forever a datetime as it has a date with:

\\\
dim x as datetime
x = new DateTime(2009,04,17)
///

This fits in every culture

(You know of course that I don't do that but to overcome that stupidity I
did)

As I do it as I normal do in VB6 then I am warned direct.

But forget that international standard, it was used in communist eastern
Europe Countries, it is used in Sweden, in one state of the USA and in
China. But it is not impossible that I with that have had them almost all.

:-)

Cor

Michel Posseth said:
Hello Cor ,

SQL parameters would also do the job and is ofcourse favored , however if
the OP is sticking to concanated SQL then ISO 8601 would sure solve his
problem
in fact ISO 8601 will work on anny platform and localization , for
instance if you ever need to write date or date time to a text file ISO
8601 is the way to go cause it will not fail .

ISO 8601 date not working direct on a US or for instance Dutch sqlServer.
Click to expand...

Well it did work when i tested it this is the format (
yyyy-mm-ddThh:mm:ss[.mmm] )

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.

HTH

Michel



Cor Ligthert said:
Michel,

Can you explain why that is in your idea better to use an ISO 8601 date
then using sqlParameters, in my idea is a ISO 8601 date not working
direct on a US or for instance Dutch sqlServer.

For those who don't know what ISO 8601 is
http://en.wikipedia.org/wiki/ISO_8601

Cor
Click to expand...
Click to expand...
 
Tomas Andersson said:
Now().ToString
Click to expand...

See the help for "DateTime.ToString Method". Since you didn't specify the
format in the call to ToString, it used the CurrentCulture(Control Panel
settings).
 
See the help for "DateTime.ToString Method". Since you didn't specify the
format in the call to ToString, it used the CurrentCulture(Control Panel
settings
Click to expand...

That is for me with dates the major reason to use sqlParameters and CDate.

Cor
 
huh ?

MS introduced ISO standards in all there "open" products, and do you know
why ? portability to other platforms
a Unix / Linux system doesn`t know the MS representation of a date time
variabel but it does know the common standards
wherein MS is also a participant .

just as ECMA etc etc ( OOXML ) MS is a particpant in these standards and
one of the standards of OOXML is that dates should be written as ISO 8601
representation so old it isn`t at all it is just a standard , just as there
is a standard that defines the size of a toilet roll in your bathroom ( this
is actually a DIN norm )
there are Toilet roll manufacturers who migh think that there roll should be
wider but then they have the risk that it will not find in the consumers
holder and so the consumer will buy a product of another toilet roll
manufacturer

Get my point ? it isn`t a law but it sure will give you an advantage to use
standards that are common and well defined especially when you are dealing
with textual data that might be used by several systems ( Legacy code , .Net
, Java ) running on different systems ( Winows , Unix , Linux ,
Mainframes )



Cor Ligthert said:
Michel,

I know, in past I was always writing this, however I stopped with it as it
was often bad understood.

However, I still initialize forever a datetime as it has a date with:

\\\
dim x as datetime
x = new DateTime(2009,04,17)
///

This fits in every culture

(You know of course that I don't do that but to overcome that stupidity I
did)

As I do it as I normal do in VB6 then I am warned direct.

But forget that international standard, it was used in communist eastern
Europe Countries, it is used in Sweden, in one state of the USA and in
China. But it is not impossible that I with that have had them almost all.

:-)

Cor

Michel Posseth said:
Hello Cor ,

SQL parameters would also do the job and is ofcourse favored , however if
the OP is sticking to concanated SQL then ISO 8601 would sure solve his
problem
in fact ISO 8601 will work on anny platform and localization , for
instance if you ever need to write date or date time to a text file ISO
8601 is the way to go cause it will not fail .

ISO 8601 date not working direct on a US or for instance Dutch
sqlServer.
Click to expand...

Well it did work when i tested it this is the format (
yyyy-mm-ddThh:mm:ss[.mmm] )

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.

HTH

Michel



Cor Ligthert said:
Michel,

Can you explain why that is in your idea better to use an ISO 8601 date
then using sqlParameters, in my idea is a ISO 8601 date not working
direct on a US or for instance Dutch sqlServer.

For those who don't know what ISO 8601 is
http://en.wikipedia.org/wiki/ISO_8601

Cor



use ISO 8601 this will work on anny system and platform







"Tomas Andersson" <[email protected]> schreef in bericht
I have a Sub with the following code
Public Sub Logg(ByVal Lvl, ByVal Designation, ByVal Uid, ByVal
FileString, ByVal ImportMode)
Dim AA As MySqlConnection = New MySqlConnection("server=" &
MYSQLServer & ";database=" & MYSQLDB & ";uid=" & MYSQLUser & ";pwd=" &
MYSQLPassword & "")
AA.Open()
Dim cmd As MySqlCommand = New MySqlCommand()
cmd.Connection = AA
cmd.CommandText = "INSERT INTO
spmon_log(DateNTime,Lvl,Designation,Uid,FileString,ImportMode) VALUES
('" & Now().ToString & "','" & Lvl & "','" & Designation & "','" & Uid
& "','" & FileString & "','" & ImportMode & "') ON DUPLICATE KEY
UPDATE ID=LAST_INSERT_ID(ID)"
Dim DR As MySqlDataReader = cmd.ExecuteReader
DR.Close()
AA.Close()
AA = Nothing
End Sub
o
Trubble is that if I run this code in my worstation a correct datetime
is registered in the database 2009-04-17 08:21:22
But this code is a part of a service running on a Server 2003 R2 and
the result is 0000-00-00 00:00:00

What can i do to correct this?
Click to expand...
Click to expand...
Click to expand...
 
Cor Ligthert said:
That is for me with dates the major reason to use sqlParameters and
CDate.
Click to expand...

If the date string came from the GUI, then it's fine, if it came from a
saved file or DB in text form, it may have been saved using a different
format. CDate() uses the control panel settings to interpret the date
string, so it could get it wrong if the supplied string was saved using a
different format. So, when saving a date, or sending it to another system,
one has to save it in only one fixed format, and Format() function is
suitable for that, or using ToString(SomeFixedFormat).

When reading back the value, DateTime.Parse(String, IFormatProvider), or
ToDateTime(String, IFormatProvider) can be used to make sure that the string
is interpreted correctly. Of course, if the date is saved in binary form,
these issues are avoided.

CDate and other Cxxx functions are good for processing input from the user
or displaying data, because during the interaction, the locale is almost
always never changed during program execution, so the format for
reading/writing is the same.
 
Back
Top