Search code examples
sqlsql-serverdatetimevarchar

Convert or cast varchar MM/DD/YYYY data into datetime MM/DD/YYYY

I'm trying to convert/cast varchar mm/dd/yyyy data into datetime mm/dd/yyyy format so that I can query a range using a between clause.

I've seen a similar question asked previously, but without the slashes in the date and I'm not sure how to work around that.

Solution

  • To do a proper range query, you can convert from your bad regional formats in a predictable way, and then use an open-ended range. Now this doesn't protect you from someone entering 06/08/2013 and meaning August 6th instead of June 8th, but if you support free text entry, that's a risk you'll have to take.

    DECLARE @s DATETIME, @e DATETIME;

SELECT @s = CONVERT(DATETIME, '03/13/2013', 101),
       @e = CONVERT(DATETIME, '03/26/2013', 101);

SELECT <cols>
FROM dbo.table_name
  WHERE datetime_column >= @s
  AND datetime_column < DATEADD(DAY, 1, @e);

    Ideally, you wouldn't allow users to type in regional nonsense like m/d/y. If you control the input format to the stored procedure, you can send an unambiguous date literal like YYYYMMDD. Or better yet, use a strongly typed parameter. The more you can take away from users entering ad hoc nonsense the less headache you'll have. Please read these articles: