2

I am pulling in invoice dates from a table in SQL Server 2008. The dates are in Julian. I've converted them using:

CONVERT(char(12), dateadd(dd, (t2.date_paid - 639906), '1/1/1753'), 101)

Great. Now, when I attempt to limit results in the WHERE clause with 

AND CONVERT(char(12), dateadd(dd, (t2.date_paid - 639906), '1/1/1753'), 101) 
  BETWEEN '07/01/2012' AND '07/31/2012'

I receive dates outside of that range. The t2.date_paid column will return dates from 2004. Thoughts?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
PuroRock
  • 73
  • 5
  • 12
  • 2
    I would recommend to use a language-independent date format if you need to use strings to represent dates. That would be the **ISO-8601** format - in SQL Server that would be `YYYYMMDD` (**no dashes!**) - so in your case, use `.. BETWEEN '20120701' AND '20120731'` to be on the safe side – marc_s Sep 15 '12 at 01:14

1 Answers1

0

You are converting the date you create with dateadd to a string so you are doing a string comparison instead of a date comparison.

Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • Thank you Mikael very much for your input. First, I can avoid the issue by searching with the Julian date and the data pulls correctly with `and t2.date_paid between '734716' and '734746'`, this is more of a 'why' more than I can't get it fixed issue. You're absolutely correct about using 'between'. However, the solution you provided pulled no results. – PuroRock Sep 14 '12 at 20:51
  • Ok. I have edited the answer leaving the part that answers the question "why". – Mikael Eriksson Sep 14 '12 at 21:08