SQL UPDATE for date data-type

[Guest]

Hi folks,

I have 2 code snippets. One works and one does not.

This first hard-coded snippet works

Dim queryString As String = "UPDATE [rfi] SET [dateresolved] = '8/16/2006'
WHERE ([rfi].[id] = @id)"

This second snippet does not work

Dim dtNow As DateTime = DateTime.Now
dRes = dtNow.ToShortDateString
Dim queryString As String = "UPDATE [rfi] SET [dateresolved] = dRes WHERE
([rfi].[id] = @id)"

dateresolved is a Date/Time data-type. The first snippet uses single quotes
around the date but I would think the second snippet would be accepted as a
string. If I debug the value of dRes, I receive the proper format of
"8/16/2006"

Any clues you can leave me with?

Thanks,
- Glenn

 

[Mark Rae]

Dim dtNow As DateTime = DateTime.Now
dRes = dtNow.ToShortDateString
Dim queryString As String = "UPDATE [rfi] SET [dateresolved] = dRes WHERE
([rfi].[id] = @id)"

dateresolved is a Date/Time data-type. The first snippet uses single
quotes
around the date but I would think the second snippet would be accepted as
a
string. If I debug the value of dRes, I receive the proper format of
"8/16/2006"

Yes, but SQL Server doesn't know that. If you're building up your SQL
dynamically, then you need to format it properly...

Dim queryString As String = "UPDATE [rfi] SET [dateresolved] = '" &
dRes.ToString("dd MMM yyyy") & "' WHERE ([rfi].[id] = @id)"

Any clues you can leave me with?

Use parameterised queries or, better still, stored procedures.

 

[bruce barker \(sqlwork.com\)]

in your sql statement

UPDATE [rfi] SET [dateresolved] = dRes
WHERE ([rfi].[id] = @id)"

dRes is assumed to be a column name, as variables start with a "@". change
it to a var and pass the parameter.

-- bruce (sqlwork.com)

 

[Guest]

Mark,

Thanks. It works. Hurray !!!

I'm sorry I did not think to concatenate things. Just not thinking, I guess.

- Glenn

Dim dtNow As DateTime = DateTime.Now
dRes = dtNow.ToShortDateString
Dim queryString As String = "UPDATE [rfi] SET [dateresolved] = dRes WHERE
([rfi].[id] = @id)"

dateresolved is a Date/Time data-type. The first snippet uses single
quotes
around the date but I would think the second snippet would be accepted as
a
string. If I debug the value of dRes, I receive the proper format of
"8/16/2006"

Yes, but SQL Server doesn't know that. If you're building up your SQL
dynamically, then you need to format it properly...

Dim queryString As String = "UPDATE [rfi] SET [dateresolved] = '" &
dRes.ToString("dd MMM yyyy") & "' WHERE ([rfi].[id] = @id)"

Any clues you can leave me with?

Use parameterised queries or, better still, stored procedures.