81995

How to convert varchar to date only when it contains a valid date?

Question:

I am looking for what I think to be a very useful information regarding SQL Server Management Studio.

I have a table with a column of type varchar that stores dates, numbers, and strings.

Those dates are stored with the following format:

dd/mm/aaaa

I have a query that searchs form matching rows and one requirement is that the user must be able to seacrh between dates (period of time).

There is no mistery if I had just dates, I could use a query:

where convert(datetime,a.valor,103) between '01/01/2013' and '03/01/2013'

The problem is that this query fails when reach a row that the value is not a date.

What would be a efficient way to perform that query since there could be thousand of rows to search?

Answer1:

The typical answer is to add a WHERE clause:

WHERE ISDATE(a.valor) = 1

However this is problematic in your situation for a couple of reasons:

<ol><li>

ISDATE() won't necessarily match the way you want depending on regional settings of the server, the user's language or dateformat options, etc. For example:

SET DATEFORMAT dmy; SELECT ISDATE('13/01/2012'); -- 1 SET DATEFORMAT mdy; SELECT ISDATE('13/01/2012'); -- 0 </li> <li>

You can't really control that SQL Server will try and perform the CONVERT after the filter.

</li> </ol>

You can't even use subqueries or CTEs to try and separate the filter from the CONVERT because SQL Server <em>can</em> optimize the operations in the query in whatever order it deems more efficient.

For example, with a limited sample, you will probably find that this works okay:

SET DATEFORMAT dmy; SELECT valor, valor_date FROM ( SELECT valor, valor_date = CONVERT(DATE, CASE WHEN ISDATE(valor) = 1 THEN valor ELSE NULL END, 103) FROM dbo.mytable WHERE ISDATE(valor) = 1 ) AS sub WHERE valor_date BETWEEN '01/01/2012' AND '01/03/2012';

But I have seen cases with even this construct where SQL Server has tried to evaluate the filter first, leading to the same error you're currently getting.

<hr />

A couple of safer workarounds:

<hr />

Add a computed column, e.g.

ALTER TABLE dbo.mytable ADD valor_date AS CONVERT(DATE, CASE WHEN ISDATE(valor) = 1 THEN valor ELSE NULL END, 103);

To protect yourself from possible misinterpretations at runtime, you should specify dateformat before issuing a query that references the computed column, e.g.

SET DATEFORMAT dmy; SELECT valor, valor_date FROM dbo.mytable WHERE ...; <hr />

Create a view:

CREATE VIEW dbo.myview AS SELECT valor, valor_date = CONVERT(DATE, CASE WHEN ISDATE(valor) = 1 THEN valor ELSE NULL END, 103) FROM dbo.mytable WHERE ISDATE(valor) = 1;

Again, you'll want to issue a SET DATEFORMAT when querying the view.

<hr />

Use a temp table:

SELECT <cols> INTO #foo FROM dbo.mytable WHERE ISDATE(valor) = 1; SELECT <cols>, CONVERT(DATE, valor) FROM #foo WHERE ...;

You may still want to use DATEFORMAT to protect yourself from conflicts between ISDATE and user settings.

<hr />

And no, you should <em>not</em> try to validate your strings as dates using string pattern matching as was suggested in another (now deleted) answer:

like '%__/%' or like '%/%'

You will have to have some pretty complex and heavy-handed validation there to handle all valid dates including leap years.

Answer2:

You can compare the table to a table that just has dates in it. It is probably worth creating a permanent table with each day, but you can use a CTE (up to 32767 recursions which gets you to 1923):

create table tmpT ( val nvarchar(255) ) go insert into tmpT values ('01/01/2012') insert into tmpT values ('jellybeans') insert into tmpT values ('21/11/2002') insert into tmpT values ('ice cream') insert into tmpT values ('30/08/2012') go ; with dates (d) as ( select d = cast('1/19/2013' as datetime) -- quick way to drop hh:mm:ss union all select dateadd(dd, -1, d) from dates where d > '01/01/1990' ) select * From tmpt join dates on convert(varchar, dates.d, 103) = tmpt.val where d between '01/01/2013' and '01/03/2013' option (maxrecursion 32767) -- max value: select datediff(dd, -32767, getdate()) = 1923

ETA: Yes I am sitting in front of a sql 2005 so no date data type for me, but the concept remains the same.

Recommend

  • jquery datepicker remove .ui-state-highlight class onSelect event
  • Pandas dataframe - select all user's rows if any of his rows contains certain value
  • Parse datetime with lubridate
  • Converting integer format date to double format of date
  • Convert date to day-of-week in R
  • Inclusion/Exclusion of rows in a DataFrame, based on specific criteria
  • Change multiple character columns to date
  • How to handle multiple date formats?
  • jquery datepicker multiple instances
  • Easiest way to parse date in String format to GregorianCalendar
  • Android: I need to delay a Notification
  • creating an initialization and deleting function for a c++ multi-dimensional pointer to class
  • StructureMap: Multithreaded env. No default instance defined for PluginFamily
  • Android /java Time Format?
  • What is the SimpleDateFormat pattern that parses like the deprecated String constructor for Date?
  • Using JQuery Datepicker To Only Show Months
  • NUnit 3.0 TestCase const custom object arguments
  • What does 'Language neutral' mean with regard to MAKELANGID?
  • Visual Studio 2010 debugger build correctly - compiler pdb and linker pdb not in synch?
  • How to get Eclipse Oxygen to run on Java 9
  • Android activity accessing service's static reference before the service is ready
  • MailKit: The IMAP server replied to the 'EXAMINE' command with a 'BAD' response
  • Switching to Release Build causes runtime error in Web Reference
  • Deleting and Updating values from a cusrsor adapter
  • Resize panoramic image to fixed size
  • Volusion's generic SQL folder, functionality
  • Modifying destination and filename of gulp-svg-sprite
  • 'TypeError' while using NSGA2 to solve Multi-objective prob. from pyopt-sparse in OpenMDAO
  • Display Images one by one with next and previous functionality
  • ORA-29908: missing primary invocation for ancillary operator
  • How to get next/previous record number?
  • XCode can't find symbols for a specific iOS library/framework project
  • NSLayoutConstraint that would pin a view to the bottom edge of a superview
  • How to set the response of a form post action to a iframe source?
  • How do you join a server to an Active Directory (domain)?
  • coudnt use logback because of log4j
  • Change div Background jquery
  • How does Linux kernel interrupt the application?
  • Qt: Run a script BEFORE make
  • reshape alternating columns in less time and using less memory