I have a field in my table of type varchar which has date data "Nov 30 of 2018" something like that, when I do the select I convert it with convert(varchar, field, 103) Date and everything is fine. The problem lies when I try to do a where to that field by placing a date range, it gives me an error for trying to convert a varchar to datetime. If anyone has an idea considering the data I have in that table please help me.
Thanks.
Unfortunately the solution is to "process" any date patterns you have and normalize the data into a format that can be converted to a date. Until the examples you have shared, we could say that the data comes in the form of:
<nombre mes en español de 3 caracteres> <día como entero> <año tipo YYYY> <HH:MM(AM/PM)>
. If that were the pattern in all the rows, we could apply the following criteria to extract the data:There are several ways to "separate" each part of the chain, I'm going to the simplest way (although somewhat "strange"), which is the function
PARSENAME()
that is designed to separate the parts of an IP address, but which is useful in this case if We convert spaces to points. Let's look at an example:The query would be:
Finally the result:
The only thing left is to concatenate the three columns, respecting the format
ISO
:YYYMMDD
and now convert to onedate
or onedatetime
using the functionCONVERT()
The problem is because varchar doesn't allow you to use a date range in a WHERE. For that you can leave it in date format or convert it to int with the yyyymmdd format.
For this you can use
CONVERT(varchar(8), campo_fecha, 112)
If you are using SQL Server 2015 onwards, I suggest you do the following:
Now, if what you are receiving in @ date1 and @ date2 are not dates, they are strings in date format, you can also use the same
In case they do not have the correct format and you have a way to receive the data separated by day, month and year, then I suggest you use SQL's own function that allows you to generate the dates having the day, month and year
I hope it is useful for you