Insert Date into SQL

[vlitim]

I am using the below code to insert a date into a date/time field in SQL. The problem I have is that it wlays inserts the date 01/01/1900 into the table column and I can't figure out any reason for this.

SQLInsert = "INSERT INTO projects(date) VALUES (" & newDate &&quot"
Set user = Con.Execute(SQLInsert)
Set user = Nothing

 

[woyler]

I encountered the same problem last week. Here is the link to the discussion:
thread222-112566 here is a small function you can use that should take care of it:
'''''''''SQL Call''''''''''
sSQL = "INSERT INTO TABLE(DATEFIELD)" & _
"VALUES(" & SQLDate(MyDate) & &quot"
'''''''''''''''''''''''''''
Public Function SQLDate(ByVal pdtDate As String) As String

If pdtDate = "" Then
SQLDate = "CONVERT(DATETIME,NULL)"
Else
SQLDate = "CONVERT(DATETIME," & SQLStr(Format$(dtDate, "yyyy/mm/dd&quot) & &quot"
End If

End Function ' SQLDate

 

[shaddow]

The problem is that u must put the date value in quotes 'date' this is SQL statment

newDate=Date
SQLInsert = "INSERT INTO projects(date) VALUES ('" & newDate &"')"

For me works just fine... ________

George

 

[jofarrell]

It all depends what type of database you are using as well because Access likes it formatted

SQLInsert = "INSERT INTO projects(date) VALUES (#" & newDate &"#)"
Set user = Con.Execute(SQLInsert)
Set user = Nothing

Joanne

 

[vlitim]

I havet tried and failed on all acounts of this one, putting single quotes around it results in an error of converting varchar to datetime. I really don't understand why I can't insert into a date field easily!?!?!!?!?

 

[dannyh]

It all depends on how you populate newDate to begin with. if you use -

newDate = 31/07/01

This will be converted into 1/3/1900 before it ever goes near your database ! set a breakpoint on the line that builds SQLInsert and check that this has got the correct format at this point. If it has not you may need to build the date using -

newDate = "31/07/01"

If you are getting the date from a datasource then check this is correct before you populate newDate.

Also make sure that you are have not got an american date format set.

When updating the database access will accept ' or # to enclose the date.

Dan.

 

[vlitim]

I am using SQL server so I presume it is slightly different to access. I have also checked and it is the correct date in the Update statment

 

[woyler]

vlitim,
Have you tried the CONVERT function?

 

[shepherd]

I believe that dannyh is on the right track. I encountered something similar once when working on localization for a program that was used in different regions. I suspect that the SQL server installation that you're using is using a different date format than the format you're trying to pass to it. For example, if SQL Server is expecting a date format of mm/dd/yyyy, it's not smart enough to convert a date passed as dd/mm/yyyy. You'll get the exact same error that you earlier described.

Check out this article on MSDN. It may offer more help...

http://support.microsoft.com/support/kb/articles/Q173/9/07.ASP

 

[woyler]

That would make sense. What led me on trying to resolve this same problem with SQL server last week was the passing of null values or zeros into a datetime field in the database. The code I provided will will account for that but not a invalid date format.
vlitim,
what is the date format that you are using?

 

[shaddow]

Try to use date/time type of the field not varchar... ________

George

 

[woyler]

George, I believe he is. Thats what was causing the issue. By default, in SQL Server, bad data or a null will show as 01/01/1900

 

[vlitim]

it looks like the best way to do it would be to convert the date to YYYYMMDD. The problem is that when I use the

convert(date,"YYYYMMDD&quot

i get an error - sub or function not declared (convert)

what am I doing wrong now??

 

[dannyh]

As convert is a SQLServer function you need to pass it as part of your SQL string. (see woylers function in this thread )

Dan.

 

[madlarry]

One way I have got round problems with inserting dates into SQL Server is by formatting the date into its long value ...

Format(strDate, "DD MMMM YYYY&quot

This way SQL Server implicitly converts it into whatever style (US or British etc) that the server is set up for.

Madlarry

 

[vlitim]

cheers for all the help everyone

finally got it working using the convert function

at last I can go and do something else!