CODEWITHSUNDEEP
sqlsql-server-2008
PuroRock
PuroRock

Reputation: 83

SQL Converting Int to Date or Char

My dbase has date formats in 6 digits with a int datatype ('734503' = 01/01/2012). I have been able to successfully convert these to char with:

SELECT CONVERT(char(12),dateadd(dd,(date_paid -    639906),'1/1/1753'),101)  
FROM   vouchers       
WHERE date_paid =    '734503'

This gives me the output of 01/01/2012. But if I search on the converted date in a query like this:

SELECT CONVERT(char(12),dateadd(dd,(date_paid - 639906),'1/1/1753'),101) 
FROM vouchers    
WHERE date_paid >= '09/01/2012'  AND date_paid <= '09/30/2012'

Why doesn't the conversion take place? Even if I use a CONVERT in the WHERE statement on the date_paid field, shouldn't it work there?

I suppose my question is how do I search with the converted character date and not have to use the 6 digit date?

Upvotes: 1

Views: 3298

Answers (2)

Charles Bretana
Charles Bretana

Reputation: 146499

First of all, If you can, change your database schema. The datatype of the date_paid column Should be datetime or date, not 'int' or 'char`.

... but if you are stuck with this schema, then it seems your date_paid values are integers where a value of 693596 represents 1 jan 1900. (This date is where the SQL Server zero value maps to.) So all you need to do is shift, or offset your search values by this amount.

Assume the date you want to search for is today, 19 October 2012

 Declare @SearchDate DateTime = '20 Oct 2012'

Then write your query predicate as:

  Select * From vouchers
  Where date_paid = DateDiff(day, 0, @searchDate) + 693596

Make sure that you structure your query so all processing or computations are done on the other side of the equal sign from the date_paid column, (as my example shows). This way the query processor can use an index on date_Paid if one exists. If the query had any function calls or other calculations on date_Paid, the query will have to read the whole table and cannot use an otherwise useable index See SARGable.

By the way, I was curious as to exactly where this design (Where integers this large are used to represent dates) came from, and I noticed that this value is exactly equal to 1900 * 365, which would mean that in this scheme, a zero value reptresetns 1 January in the year zero (when Christ was born) which would be cool, except that you can't use it in a SQL Server date cause SQL Server values represent the date as a 16 bit signed integer, which maps zero to 1 jan 1900, and are limited to the smallest value in a 16 bit integer (-16,384) which equates to some date in 1753 or so.

Upvotes: 1

MatBailie
MatBailie

Reputation: 86715

Your problem is that you are comparing an INT column with STRING values, and expecting it to resolve as if comparing dates...

WHERE date_paid >= '09/01/2012'
  AND date_paid <= '09/30/2012' 

-- date_paid is an INT (according to your question)
-- '09/01/2012' is a STRING

Because of the differing datatypes there is an implicit CAST() in there. Effectively you're doing...

WHERE CAST(date_paid AS VARCHAR(10)) >= '09/01/2012'
  AND CAST(date_paid AS VARCHAR(10)) <= '09/30/2012' 

-- NOTE:  All of these values are now strings
--        They may LOOK like dates, but they're just strings

What you really need to do is explicitly manipulate the strings in to integers.

WHERE date_paid >= DATEDIFF(DAY, '01/01/1753', '09/01/2012') + 639906
  AND date_paid <= DATEDIFF(DAY, '01/01/1753', '09/30/2012') + 639906

This too involves implicit casts. DATEDIFF() only takes DATETIME datatypes so the strings are implicitly cast to DATETIMEs first.

EDIT:

Another option would be to CAST() the date_paid field into a DateTime, then base the WHERE caluse on that. The down side here is...
- The CAST() would have to be done on every row, then the WHERE clause applied
- This prevents any use of indexes and dramatically reduces performance

The answer above does all the manipulation on the constant values, so that the searched field can be processed in it's native state; thus allowing use of indexes.

Upvotes: 1

Related Questions