CODEWITHSUNDEEP
sqlt-sqldatetimedate
Nick LaMarca
Nick LaMarca

Reputation: 8188

Comparing String Date To A Datetime

I have a stored proc that takes a date a string parameter. The data coming in looks like the form yyyy-MM-dd in the database the dates are save in the form 2012-02-26 06:00:00.000 (a time is attached to them. When I run the sql it always returns nothing because it cant find a comparison:

'select t_typday from ' + @dbName+ '.dbo.Calendar ' +
' where T_date LIKE ''' + @dateFilter + '%''';

Upvotes: 1

Views: 1624

Answers (2)

Michael Goldshteyn
Michael Goldshteyn

Reputation: 74330

You need to convert the date in your table to the yyyy-mm-dd hh:mi:ss.mmm date format before comparing it with your string parameter in that format using LIKE:

To fix your existing code with minimal changes, change T_date to CONVERT(VARCHAR(50),T_date,121), producing:

'select t_typday from ' + @dbName+ '.dbo.Calendar ' + 
' where CONVERT(VARCHAR(50),T_date,121) LIKE ''' + @dateFilter + '%''';

If you do not perform the conversion, you are comparing a local format string (based on the @@LANGUAGE setting), such as Apr 12 2012 12:00AM with your string in @dateFilter which contains, for example, 2012-04-12. This is why the comparison is not working.

Of course passing in a parameter of type DATE into your stored procedure would be a much better solution, as several comments have already mentioned. That would allow you to compare the date in T_date with the passed in DATE value in @dateFilter using a simple expression such as:

CONVERT(DATE,T_date)=@dateFilter

Upvotes: 1

Oded
Oded

Reputation: 498904

You need to use an unambiguous date format - something that can't be interpreted as several possible dates.

See this and this for options.

Upvotes: 1

Related Questions