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.
| Author |
Topic |
|
brettl2
Starting Member
2 Posts |
 Posted - 2011-01-12 : 10:51:07
|
| Hi, trying to read data from an excel file and write to SQL 2005 db.The following SQL statement: INSERT INTO dbo.TestWriteTable(RTUno, Owner, Ldate, MP, MPValue, Vsize) Values(" & hvRTUno & ", " & hvOwner & " , " & Format(hvLdate, "DD/MM/YYYY HH:MM:SS") & " , " & hvMP & " , " & hvMPValue & " , " & hvVSize & ")when executed becomes:INSERT INTO dbo.TestWriteTable(RTUno, Owner, Ldate, MP, MPValue, Vsize) Values(200, 920 , 19/10/2010 17:00:00 , 1 , 0.12 , 300)I am getting the error :Line 1: Incorrect syntax near '17'the '17' refers to the hour from the value I am trying to write to the column Ldate, which is of datatype datetime.Can anyone see why I am getting this error? - I have formatted the data as YYYY/MM/DD HH:MM:SS and get the same result.Any help would be much appreciated.Liam. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-01-12 : 10:53:07
|
its because of date value. you need to pass it within '. so statement should be likeINSERT INTO dbo.TestWriteTable(RTUno, Owner, Ldate, MP, MPValue, Vsize) Values(" & hvRTUno & ", " & hvOwner & " , '" & Format(hvLdate, "DD/MM/YYYY HH:MM:SS") & "' , " & hvMP & " , " & hvMPValue & " , " & hvVSize & ")------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
brettl2
Starting Member
2 Posts |
Posted - 2011-01-12 : 10:59:04
|
quote:
Originally posted by visakh16
its because of date value. you need to pass it within '. so statement should be likeINSERT INTO dbo.TestWriteTable(RTUno, Owner, Ldate, MP, MPValue, Vsize) Values(" & hvRTUno & ", " & hvOwner & " , '" & Format(hvLdate, "DD/MM/YYYY HH:MM:SS") & "' , " & hvMP & " , " & hvMPValue & " , " & hvVSize & ")------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Thank you for your help, it is much appreciated!Liam. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-01-12 : 11:04:06
|
welcome one suggestion is try to sent date values in yyyymmmdd hh:mi:ss format to avoid ambiguity------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
Incorrect syntax near...