problems with datetime

[gd082]

hi

I have a little problem. I have to display, insert and update dates from our database (sql server). To display the dates isn't a problem. But when I try to insert a date he isn't doing anything.

Sub set_date()
Dim strsql As String
Dim dataset1 As New DataSet()
strsql = "UPDATE ... SET ...=('" & DateTime.Now "') "
strsql += "WHERE ... "
End Sub

 

[gd082]

strsql = "UPDATE ... SET ... = (" & Today.Date.ToShortDateString & &quot "
strsql += "WHERE ... "

With this function I can insert a date in the database. The problem is he always sets the date too 1/01/1900... I think I wasn't alive at that time :s

 

[ja2000]

there's a little difference between datetime on the .net framework and the sql server... .net framework datetime is stored as this mm/dd/yyyy while the sql server's format of datetime is yyyy-mm-dd...

so i guess you should've convert your datetime to sql server's format first before you insert the datetime. for example :
in .net DateTime.Today = 03/23/2002
when you insert it directly to sql (without converting it first), the sql won't recognize it as a valid date... it should've thrown an exception (i don't know why in your case the sql server didn't throw any exceptions)

the second way, you could use a stored procedure which accept a datetime of mm-dd-yyyy and convert it using sql's convert function to a yyyy-mm-dd format

anyway, there's another way to simplify your life ... you could use the Microsoft's DataAccess Application Block assembly. that assembly contains SqlHelper class. i've already used that, it should make your life easier try that... you can download the assembly from msdn.

 

[jfrost10]

ja, I'm not sure the date formatting matters.

Alot of the web app we've written saves dates from .net to a sql server. We don't perform any formatting, and it saves without a problem.

gd082, could you post your entire sub for us? We could try running your code on our machines and see if we get the same problems with it.

Jack

 

[ja2000]

sorry jack ... it's only my opinions coz i don't see anything wrong with his code so i suggest him to check the datetime format

anyway, why don't you use stored procedure to reduce your problem (this way, you could reduce your problem domain to 2 areas, .net and sql server). first you try executing your stored procedures from sql query analyzer, if it's valid, then your problem is on your code . try it ^0^

 

[gd082]

I used this function :

SET @date = '" & Format(Date.Today, "MM/dd/yyyy&quot & "'

This seems to work.

greetz

 

[harmmeijer]

I had a lot of problems with date formatting, in Holland the date format is dd-mm-yyyy, the us it is mm-dd-yyyy.
If you have mixed nationalitys using your app it will be a problem.
Allso setting the language option of a sql user allso does something with the date (try making a user with language NL and inserting a date like 01-20-2001)
To make sure that users supply a date in a strict format I made a popup calendar (vb client script, this runs only in IE). You can find this calendar here:

http://www.geocities.com/harm_meijer/calendar.zip

This calendar will provide a date in us style (mm-dd-yyyy) what if the sql database wants it in NL style (dd-mm-yyyy).
No problem, here is where the user tlbroadbent helped me out.
Tell sql server that in this specific query you will supply a date in the mm-dd-yyyy format like so:
SET DATEFORMAT mdy -- in this query i will supply a date in the format mm-dd-yyyy
set Nocount ON -- this is an insert and I want the id of the inserted record
insert into table1 (dte) values ('08/27/72') --
select @@identity as newrec -- give me the id of the inserted record
set nocount off

Need to know more about date hanling in sql server?
check tlbroadbent's thread
thread183-223510