SQL Server – Searching Between Dates on a CHAR Type Field

sql server

I have a table that has a field in which dates are stored, the problem is that the field type is CHAR(10) and I'm being asked to query for data between certain dates.

I don't want to change the field type because I'm not sure how's that gonna affect the frontend application.

Is there a way of querying for results between dates without making changes to the data type?

Regards.

Best Answer

You can cast your date to perform where clause on it just like this.

WHERE CONVERT(DATETIME, MyStringDateField, 120) BETWEEN @DateParam1 AND @DateParam2

The last parameter in CONVERT is the date format. You must choose the one that match your text data of your date. You can find the list here

You must be aware that if a string does not match a date for a particular record, your query will fail with a message saying that the data XYZ cannot be converted to DATETIME type.

Related Solutions

Sql-server – How to determine why the stored procedure is getting deadlocked

Have you checked to see if any stats on the tables in question are out of date? When I've seen issues like this, it's usually a highly-fragmented index (or missing index) or stats that are woefully out of date; as a result, the server spends way too much time sorting through data and not nearly enough actually running the query.

SQL Server Foreign Key – Handling Different Datatypes (char(8) vs varchar(8))

Digging into long forgotten boxes I found both nt4 and SQL-Server 6.5 installation media and I can confirm that a foreign key referencing fields with different data types was allowed.

Here is the statement I successfully run on SQL-Server 6.5:

create table tbl1 (code char(8) primary key (code))
create table tbl2 (code varchar(8) primary key (code))
alter table tbl1 add constraint FK foreign key (code) references tbl2 (code)

no trailing semicolon or you'll get a syntax error.

I can't get a grasp on sql 7 media so I can't tell if it was working on that version too.

Best Answer

Related Solutions

Sql-server – How to determine why the stored procedure is getting deadlocked

SQL Server Foreign Key – Handling Different Datatypes (char(8) vs varchar(8))

Related Question