CODEWITHSUNDEEP
sqlsql-server-2008-r2
JL.
JL.

Reputation: 81342

SQL Statement help needed

I have a table where the date is being stored as a varchar, in the format DD.MM.YYYY.

I've created a stored proc that will query this table and return results based on Month and Year.

I'll have 2 input parameters being MM and YYYY - both coming in as strings.

What would the where clause be to get all items that match the month and year? I suspect I need to use a LIKE operator because it is a varchar field.

My SQL is rusty, thanks for the assistance.

Upvotes: 0

Views: 71

Answers (4)

Rich Adams
Rich Adams

Reputation: 26584

WHERE field LIKE "%." + @month + "." + @year

However, the best way would be to create a new column with the correct data type, run a script to populate the correct datetime values in the new column, then ditch the varchar column and use MSSQLs built in ways of searching a datetime field. This will give you much faster lookups in the long run.

WHERE MONTH(field) = @month
      AND YEAR(field) = @year

Upvotes: 1

Jacob
Jacob

Reputation: 43299

SELECT * FROM t1 WHERE RIGHT(datecol,7)= @StringMM + "." + @StringYYYY

Upvotes: 0

Chandu
Chandu

Reputation: 82943

Try this(assuming @MM and @YYYY are your Input Parameters):

SELECT *
  FROM <YOUR_TABLE>
 WHERE <DATE_COLUMN> LIKE '[0-9][0-9].' + @MM + '.' + @YYYY

Upvotes: 2

Ovais Khatri
Ovais Khatri

Reputation: 3211

 Select * from table1 t
    where Month(Cast(t.DateCol as DateTime)) = Cast("1" as bigint)
    AND Year(Cast(t.DateCol as DateTime)) = Cast("2011" as bigint)

Upvotes: 1

Related Questions