Parameterized SQL Commands

[sdoc]

Can T-SQL functions like Convert,Cast etc be used in a Parameterized SQL command ?

Here is the code I was tryign to use which would not work

Dim sqlStr As String
sqlStr = "Update ClientTransactions"
sqlStr = sqlStr & "  SET TranId=@TranId,[B][COLOR=Purple]CtrnTime = convert(nvarchar,CtrnDate,101)+' '+ @CtrnTime[/COLOR][/B],CtrnDuration=@CtrnDuration,CtrnNotes= @CtrNnotes,LastModifiedDate=GETDATE(),UserIdLastModified=@UserIdLastModified"
sqlStr = sqlStr & "  where RecurTranID=@RecurTranID"
Dim cmd As New SqlCeCommand()
cmd.CommandText = sqlStr
cmd.CommandType = CommandType.Text
cmd.Parameters.Add("@TranId", SqlDbType.NVarChar).Value = ConvertToSql(TranID)
cmd.Parameters.Add("@CtrnTime", SqlDbType.NVarChar).Value = ConvertToSql(CtrnTime.ToString("HH:mm:ss"))    
cmd.Parameters.Add("@CtrnDuration", SqlDbType.Int).Value = ConvertToSql(CtrnDuration)
cmd.Parameters.Add("@CtrnNotes", SqlDbType.NVarChar).Value = ConvertToSql(CtrnNotes)
cmd.Parameters.Add("@RecurTranID", SqlDbType.NVarChar).Value = ConvertToSql(RecurTranID)
cmd.Parameters.Add("@UserIdLastModified", SqlDbType.NVarChar, 40, "UserIdLastModified").Value = ConvertToSql(UserId)
cmd.Parameters.Add("@LastModifiedDate", SqlDbType.NVarChar, 40, "LastModifiedDate")
ExecuteNonQuery(cmd)

is the part marked in purple correct?

 

[cjard]

They can, but your SQL looks a bit broken.. Why dont you jsut store the time properly, instead of trying to shove a string of (ctrndate converted into a 101 style date with a space and the string representation of ctrtime added onto the end), hoping that it all converts back properly?

i.e. Asked another way; why do people complain that date/time ops dont work with their database, when the SQL they write usually has the database guessing how to convert a date into a string and back again several times?

Store the date/time as a date/time. If you want it to look like a time in the frontend app, format it so the date portion goes away:

myDateTime.ToString("HH:mm:ss")

this will let you do all sorts of funky things with date math, comparison etc..



It is better to store precision, and use fuzz, than to store fuzz, and have to attempt to add precision just so you can reduce it to another form of fuzz..

 

[sdoc]

Say I have a bunch of records for different dates but same time with the common id being RecurTranID
here is what i want the query to do basically...
"Change the time value on all these records to hh:mm:ss but leave the date part untouched"

so say records are
Time | RecurTranID
02/24/2007 11:00:00 | 101
02/25/2007 11:00:00 | 101
02/26/2007 11:00:00 | 101

after you run the query to change time to 15:00:00 they should be

Time | RecurTranID
02/24/2007 15:00:00 | 101
02/25/2007 15:00:00 | 101
02/26/2007 15:00:00 | 101

 

[cjard]

I dont usually work with SQLServer, so asking in that forum might be better. In oracle, I would do this:

UPDATE table SET dateCol = trunc(datecol) + (15/24)

which would be very, very fast.


Or this:

UPDATE table SET dateCol = to_date(to_char(dateCol, 'YYYYMMDD') || '150000', 'YYYYMMDDHH24MISS')

which would be slower..

I know for certain that SQLS cant exactly do the second one because its date handling is very lame compared to Oracle. You might try this:

UPDATE table SET dateCol = Floor(dateCol) + (15/24)


Alternatively, implementing these functions:
http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx

Might help you out quite a lot..

 

[cjard]

Ps, if the are always 11:00:00 you can

Update table SET dateCol = DATEADD(datecol, 4, hh)

or something..