Don't you just love 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

General SQL Server Forums

New to SQL Server Programming

Don't you just love datetime

Author

Topic

Ishamael
Starting Member

14 Posts

Posted - 2006-08-14 : 02:31:47

Hey, I'm sorry for posting without checking the entire forum yet:) been on a few other sites, this is probley a basic problem but I can't figure it.

Simply put I am using Access VBA to insert into a table.. which contains 3 datetime columns. However I get a char has given out of range datetime value. Or i can get 01/01/1900. Or sometimes I get a date that looks to be 01/01/1900 minus the current date.

Tryed formating, using/not using the 's.

And I know this is access stuff.. my main question is, is the problem a SQL server 2005 error or a vba one.
Sorry if this is in the wrong place and thanks for your time anyway:)

I won't be back for a little, home time for the day. I'll be back in the office early tomorrow.

This is a demo string that is sent to the SQL Server.. messy:(

INSERT INTO dbo.DeliveryPackages
(Status, Long_Description, Short_Description, Career, Start_Term, Owning_Campus, Start_Date, End_Date, Student_Quota, Formal_Description, PEP_Prog_Activate, PEP_DP_Activate, International, Class_Delivery, QTAC, QTAC_Code, Owning_User, Modified_User, Modified_Date, IO_Level, IO_Number, Acad_org, Fund_source, Approved, Modified, DP_Notes)
values
('2', 'TNQMBASPMF,', 'hjk', '2', '3700', 'TNQ', 25-01-2016, 31-01-2018, '57', 'gjkhjk', 'False', 'True', 'True', '1', 'True', 'Pending', 'mansonj', 'mansonj', 14-08-2006, '2', 'New IO', 'TNQATSG101', 'BHE', '0' , '1', '')

The code is this.. sorry for the formating and the 'easy' to understand names:P

***Just sets up the variables/connection etc***
Dim conn As ADODB.Connection
Dim strSQL As String
Dim strColumns As String
Dim strValues As String
Set conn = CurrentProject.Connection
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

Dim strTimestamp As String
Dim strStartDate As String
Dim strEndDate As String

strStartDate = Format(txtStartDate.Value, "dd-mm-yyyy")
strEndDate = Format(txtEndDate.Value, "dd-mm-yyyy")
strTimestamp = Format(Date, "dd-mm-yyyy")

cmd.ActiveConnection = conn
cmd.CommandType = adCmdText

***Building the Columns String***
strColumns = "Status, Long_Description, Short_Description, Career, Start_Term, Owning_Campus, Start_Date, End_Date, "
strColumns = strColumns & "Student_Quota, Formal_Description, PEP_Prog_Activate, PEP_DP_Activate, International, "
strColumns = strColumns & "Class_Delivery, QTAC, QTAC_Code, Owning_User, Modified_User, Modified_Date, IO_Level, "
strColumns = strColumns & "IO_Number, Acad_org, Fund_source, Approved, Modified, DP_Notes"

***Building the Values String***
strValues = "'" & cboStatus.Value & "', '" & txtLong.Value & "', '" & txtShort.Value & "', '" & cboCareer.Value & "', '" & cboStartTerm.Value
strValues = strValues & "', '" & txtOwningCampus.Value & "', " & strStartDate & ", " & strEndDate & ", '" & txtStudentQuota.Value
strValues = strValues & "', '" & txtFormalDescription.Value & "', '" & CBool(chkProg.Value) & "', '" & CBool(chkDP.Value) & "', '" & CBool(chkInternational.Value)
strValues = strValues & "', '" & cboDelivery.Value & "', '" & CBool(chkQTAC.Value) & "', '" & txtCode.Value & "', '" & txtusername.Value
strValues = strValues & "', '" & txtusername.Value & "', " & strTimestamp & ", '" & txtLevel.Value & "', '" & txtIOnumber.Value & "', '" & txtAcad_org.Value
strValues = strValues & "', '" & txtfund.Value & "', '0' , '1', '" & txtDP_Notes.Value & "'"

***This puts the string together and sends it away***
strSQL = "INSERT INTO dbo.DeliveryPackages (" & strColumns & ") values (" & strValues & ")"

cmd.CommandText = strSQL
cmd.Execute

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-14 : 02:46:58

your insert string should be like this


INSERT INTO dbo.DeliveryPackages
(Status, Long_Description, Short_Description, Career, Start_Term, 
Owning_Campus, Start_Date, End_Date, Student_Quota, 
Formal_Description, PEP_Prog_Activate, PEP_DP_Activate, International
, Class_Delivery, QTAC, QTAC_Code, Owning_User, Modified_User, 
Modified_Date, IO_Level, IO_Number, Acad_org, Fund_source, Approved,
 Modified, DP_Notes)
values
('2', 'TNQMBASPMF,', 'hjk', '2', '3700', 'TNQ',' 
25-01-2016', '31-01-2018', '57', 'gjkhjk', 
'False', 'True', 'True', '1', 'True', 'Pending', 'mansonj', 'mansonj',
 '14-08-2006', '2', 'New IO', 'TNQATSG101', 'BHE', '0' ,
 '1', '')

Chirag

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-08-14 : 07:13:59

Use parameters, do not concatenate your sql like this!

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

The example given uses .net, but it still applies to your situation with ADO.

- Jeff

Ishamael
Starting Member

14 Posts

Posted - 2006-08-14 : 19:30:01

chiragkhabaria.. Ok I am never going to try pronounce that again.. Coworker thinks I've lost it... might be right. I'm not a great language person:)

Thanks for reminding me about the 's I had them there but took them out to try other things at one point.

Ok, I placed the ' ' back around the dates. I had done this before, it throws up the char converted to out of range datetime value. I have fixed this by changing the formatting lines to these:
strStartDate = Format(txtStartDate.Value, "yyyy-mm-dd hh:MM:ss")
strEndDate = Format(txtEndDate.Value, "yyyy-mm-dd hh:MM:ss")
strTimestamp = Format(Now, "yyyy-mm-dd hh:MM:ss")

I think thats the univeral format? I know that theres the real date(dd-mm).. then theres the USA(mm-dd) date system:P Neither of those worked.

jsmith8858 besides the obvious SQL Injection attacks is there any fault to doing it the way I am? I am aiming at trying to keep most the SQL seperate from the columns/values for the moment.
It's just that the project is in a Acess .adp and well even with the login system etc I have created all it takes is a few clicks to disable all of that, unfortunitly security isnt going to be huge:(

Well anyone that gets bored feel free to point me to a existing topic that explains these or write a little story about datetime:D

What I just do not understand is why it was refusing to accept normal dates.. everytime I see a datetime column I get a overwhelming feeling to teach the server to swim in the lake.

Thanks again guys :)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-14 : 20:24:03

Well, passing a date as 25-01-2016 or 31-01-2018 or 14-08-2006 is not going to work, since they are not inside quotes.

Without the quotes, 14-08-2006 becomes a numeric expression = -2000 which gets converted to a date of 1894-07-11. So, at the very least, you should enclose the date in quotes to convert to a string.


declare @d table (dt datetime )
insert into @d  values ( 14-08-2006 )
select * from @d

Results:
(1 row(s) affected)

dt                                                     
------------------------------------------------------ 
1894-07-11 00:00:00.000

Then you have another potential problem. Depending in the setting of DATEFORMAT, SQL Server may not be able to convert that to a date. If you have US date settings, MDY, it will give you an error. The best way to pass a date string to SQL Server is in format YYYYMMDD, '20060814'. This is the one format that SQl Server will always convert correctly.

As Jeff pointed out, you would be better off using SqlCommand to do this type of work, and let it worry about the details.


Dim TranDate As Date = Now.Date
cm.Parameters.Add("@TranDate", SqlDbType.DateTime).Value = TranDate

You may wnat to read the SQL Server documentation about datetime:
SQL Server 2000 Books Online
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/startsql/portal_7ap1.asp
SQL Server 2005 Books Online
http://msdn2.microsoft.com/en-us/library/ms130214.aspx

This link contains a lots of links to various datetime subjects.
Date/Time Info and Script Links
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762

CODO ERGO SUM

Ishamael
Starting Member

14 Posts

Posted - 2006-08-14 : 20:37:46

Thanks MVJ:) You've done alot of work on it :O This is going to be alot of reading :D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources