CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2008-r2
iceyb
iceyb

Reputation: 436

Filter dates stored as varchar in SQL Server

I am using SQL Server 2008 R2 on the EC2 instance. I have a database having 4 fields of all varchar(50). 

 SELECT
 *
FROM
 xyz
WHERE
 DataDate ='20140609'

This query gives no result.

SELECT
 *
FROM
 xyz
WHERE
 DataDate = (Select MAX(DataDate) from xyz)

This query runs perfectly.

Select MAX(DataDate) from xyz

This query results in 20140609.

I cannot understand why this this happening. Can someone please explain this?

Upvotes: 0

Views: 1766

Answers (3)

A. S. Mahadik
A. S. Mahadik

Reputation: 625

in sql spaces count as the character. Then use like operator to this query as

SELECT * FROM xyz WHERE DataDate like '%20140609%'

Upvotes: 2

Tanner
Tanner

Reputation: 22743

As stated in comments it's likely due to leading spaces in the values. If you do the following to remove any spaces from the values it should work:

SELECT *
FROM  xyz
WHERE REPLACE(DataDate, ' ', '')='20140609'

Alternately, you could use LTRIM / RTRIM functions to do this.

Sample SQL Fiddle

create table SampleData(myDate varchar(50))

insert into SampleData(myDate) values(' 20140609 ')
insert into SampleData(myDate) values('20140608')
insert into SampleData(myDate) values('20140607')
insert into SampleData(myDate) values('20140606')

Both of these queries work in the fiddle:

SELECT *
FROM  SampleData
WHERE REPLACE(myDate, ' ', '')='20140609'

Select MAX(REPLACE(myDate, ' ', '')) from SampleData

Future Advice:

Even so, it's not a good idea to save dates as varchar for numerous reasons so I would suggest changing that if possible.

The below would perform a conversion on your existing data to convert them to valid dates, whilst removing any spaces, assuming your dates are always in the format YYYYMMDD:

SELECT cast(REPLACE(DataDate, ' ', '') as DateTime) FormattedDate
FROM  xyz

To implement this, you could create a new DateTime column on the table and insert the correct date values in to there with something like below and then modify your code to use the new column:

First add a new column to the table:

ALTER TABLE xyz 
ADD FormattedDate DateTime NULL

Then update the data in the new column so it holds the converted dates:

UPDATE xyz
SET FormattedDate = cast(REPLACE(DataDate, ' ', '') as DateTime)

Upvotes: 2

kiranSchool
kiranSchool

Reputation: 65

Can you modify the query as below and give it a go ?

 SELECT
 *
FROM
 xyz
WHERE
 DataDate ='2014-06-09'

Upvotes: 0

Related Questions