DateTime - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

DateTime

Author

Topic

Wouter Benoit
Starting Member

23 Posts

Posted - 2007-09-17 : 07:34:17

Hello,

I'm building an application where I have to insert data into the table.
One of the data fields is the date of today.

I use the following code to do so in VB.NET

Dim date as DateTime
date = System.DateTime.Today

When I code msgbox(date) it gives me the wright date but when I want to insert it into the database I always get the error "The conversion of a char data type to a datetime data type resulted in an out of range datetime value".

The field in the SQL table where I have to store the date is from the type datetime(8).

The strange thing is that everything goes like it should until the date is greater then the 10th of each month. Then I get the above error otherwise it works just fine.

Can someone explain me why this can happen or what I can do about it, cause this is just to strange for me.

Thanks in advance

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2007-09-17 : 07:35:58

Are you using a ADO parameter to pass the date? or are you creating a string and executing it against the connection?

Also check the regional settings on your computer as to what time format your using.

Show your ado code please.

Here is an example in C#
http://www.c-sharpcorner.com/UploadFile/Ashish1/MSaccessDateInsert09022005061029AM/MSaccessDateInsert.aspx

Some extra info on datetime vb and sql server
http://www.sql-server-performance.com/articles/dev/advanced_date_datetime_handling_p1.aspx

http://weblogs.asp.net/pleloup/archive/2004/05/28/143960.aspx

Wouter Benoit
Starting Member

23 Posts

Posted - 2007-09-17 : 07:52:23

Here is the code I'm using:

Private Sub gegevens_ophalen(ByVal btn As String)

Dim datum = System.DateTime.Today

Dim oCmd As SqlClient.SqlCommand
Dim oDR As SqlClient.SqlDataReader
Dim strSQL As String
Dim strConn as String

strConn = ConnectStringBuild() //A function that's set the database connection

strSQL = "INSERT INTO tempBewegingen(datum) VALUES( & 'datum' & ")" //There are some more field to be filled up but those work fine

Try
oCmd = New SqlClient.SqlCommand()
With oCmd
.Connection = New SqlClient.SqlConnection(strConn)
.Connection.Open()
.CommandText = strSQL
oDR = .ExecuteReader
End With

Catch oExcept As Exception
MessageBox.Show("insert into tempBewegingen: " & oExcept.Message)
End Try

Thanks for the links, I'll check them out for propably a better solution :-)

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2007-09-17 : 07:54:31

What do you get if you do a debug or print or messagebox on strSQL before it gets executed?

you probably just need single quotes around you values statement

values('20070917')

Wouter Benoit
Starting Member

23 Posts

Posted - 2007-09-17 : 08:02:18

When I do msgBox(strSQL) it returns my entire SQL querie with the values that will be inserted.

The date value is '17/09/2007'

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2007-09-17 : 08:03:03

Would you mind pasting the string?

Wouter Benoit
Starting Member

23 Posts

Posted - 2007-09-17 : 08:06:31

INSERT INTO tempBewegingen(bonnummer,bonlijnnummer,productsleutel,omschrijving,aantalbeweging,aankoopprijs,aankoopeenheid,verkoopprijs,aankoopprijsperverkoopeenheid,verkoopsproduct,datum, uur,statusherdruk, stockbijhouden, conversie, verkoopeenheid, knopnummer, soort, soortbon, totaal)VALUES(5,1,13,'Cola',1,10,'bak',0.9,0.5,-1,'17/09/2007','14:04:05',0,1,24,'flesjes',1,'F','V',0.9)

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2007-09-17 : 08:06:45

SQL Default settings is mm/dd/yyyy you may have to change the regional settings on SQL Server or convert the date in VB to pass it in USA format.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-17 : 08:32:21

You should always use DATETIME parameter and express dates in YYYYMMDD format to avoid conflict with local settings

Madhivanan

Failing to plan is Planning to fail

Wouter Benoit
Starting Member

23 Posts

Posted - 2007-09-17 : 08:40:33

That seems to works.

Thanks a lot

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-17 : 08:42:51

quote:
Originally posted by Wouter Benoit

That seems to works.

Thanks a lot

Which one are you referring about?

Madhivanan

Failing to plan is Planning to fail

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2007-09-17 : 08:43:05

I use ISO 8601 sortable time pattern.

http://www.karaszi.com/SQLServer/info_datetime.asp

Wouter Benoit
Starting Member

23 Posts

Posted - 2007-09-17 : 08:46:20

The solution from ValterBorges.

I did the following

Format(system.datetime.today, "MM/dd/yyyy") and the errors are gone and the date is inserted in the database.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-17 : 08:47:50

Just use parameters!! Never, ever, ever format and convert and concatenate strings like that with a sql statement. Just declare and use a DATETIME parameter. There is no excuse to not do this.

read this: http://weblogs.sqlteam.com/jeffs/archive/2006/07/21/10728.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-17 : 08:48:08

quote:
Originally posted by Wouter Benoit

The solution from ValterBorges.

I did the following

Format(system.datetime.today, "MM/dd/yyyy") and the errors are gone and the date is inserted in the database.

Note that If the date format of the server is dmy, then you will get error for some dates. As I specified always format it to YYYYMMDD and send to the table

Madhivanan

Failing to plan is Planning to fail

Wouter Benoit
Starting Member

23 Posts

Posted - 2007-09-17 : 08:52:53

OK, I will read the document and adapt my code.
Thanks for the info.
I'm still learning to work with VB.Net and SQL server since I didn't work with it untill 3 months ago.
So I appreciate all the help you give me guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources