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:
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.
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.
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.
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.