CODEWITHSUNDEEP
sqlsql-servervb.net
user765942
user765942

Reputation: 147

where clause in select statement - datetime issues

I want to put a where clause in my select statement based on the year and month of a timestamp field in my db

I have a month and a year dropdownlist which give me the following string 01/2012

The date format in my db is "2012-01-01 00:00:00" but when I select an individual date and put it in a message box it converts to "01/01/2012"

I've altered my select statement below to reflect the converted date. However Im still not given the correct details. Any ideas? Is there a particular format that I need to use when dealing with a timestamp field? Can I even use the "Right" function in a select statement?

Dim newRecordDate As String = val1 & "/" & ComboBox2.SelectedValue 
Dim sql2 As String = "Select CatA, CatB, CatC,  Cost, Currency, MarketingCode, Comment, RecordDate from vw_tblP_Usage_Details where puid = '" & puid & "' right(RecordDate, 7) = '" & newRecordDate & "'"

Upvotes: 2

Views: 1004

Answers (1)

J Cooper
J Cooper

Reputation: 5008

I say use parameters and the SqlParameter class to pass parameter values to sql server from .NET client instead of using concatenation and string formatting. It makes life easier.

Something Like This:

Dim myDate As Date = DateTime.Now
Dim sql As String = "Select * from SomeTable where MyDate = @some_param"

Using Command As New SqlClient.SqlCommand(sql)
    Command.Parameters.AddWithValue("@some_param", myDate)

    Using reader As SqlClient.SqlDataReader = Command.ExecuteReader()

        'other code here
    End Using
End Using

Upvotes: 7

Related Questions