string with leading 0 is converting to int

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

SQL Server 2000 Forums

SQL Server Development (2000)

string with leading 0 is converting to int

Author

Topic

smithje
Starting Member

32 Posts

Posted - 2002-04-05 : 12:36:31

I am inserting a time value (military time 0000 to 2400)
into a SQL Server db. Field data type is nvarchar; FieldName is TripTime using the (ASP) INSERT below:

sql = "INSERT INTO tbl_MAQ (OfficeID, ReportDate, TripTime) " sql = sql & "VALUES("
sql = sql & "'" & varOfficeID & "'"
sql = sql & ","& "'" & cDate(varReportDate) & "'" sql = sql & ","& "'" & Request("Time" & i ) & "');"

This works fine as the Times are entered into the db as, for example 0745 or 0930. I can retrieve them from the db and the leading Zero's still exist.

But when I update the same field using:

strSQL = "Update tbl_MAQ SET TripTime = " & cStr(varTime) & " Where RecordID = " & varRecordID & ";"

The leading zero gets dropped somewhere. Even explicitily converting the data to a string cStr(varTime) has no effect. It updates the field with what appears to be an INT

An example resulting from the above Update shows TripTime = 745 or TripTime = 930, without the leading Zero.

Jeff

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-04-05 : 12:51:26

When you say "An example resulting from the above Update shows TripTime = 745 or TripTime = 930, without the leading Zero. "

are you saying this is the value that gets inserted into the DB or is this part of the value of strSQL that you did a Response.Write on?

I belive that your strSQL would look like this if you did a Response.Write

Update tbl_MAQ SET TripTime = 0745 Where RecordID = 12;

When this gets passed to SQL the 0745 is treated as a numeric and converted to 745. You need to put single quotes around 0745 just like you did in your first INSERT statement.

strSQL = "Update tbl_MAQ SET TripTime = '" & cStr(varTime) & "' Where RecordID = " & varRecordID & ";"

smithje
Starting Member

32 Posts

Posted - 2002-04-05 : 16:51:47

Thank You, Yakoo
That did it! The old single quote text definer.
My partner thanks you. You stopped his hair loss, for today.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources