-4

I have saved dates as VARCHAR in sql server 2005 now I want to select all dates before 2013 and I can't.

WDATE 09/01/2012 08/13/2012

I want to compare those dates with any other date I am using this query:

SELECT * FROM Work_Order
WHERE  WDATE  < '09/02/2012'

but it subtract days then months then years which is not my case

Ahmed Kato
  • 1,697
  • 4
  • 29
  • 53

1 Answers1

0

Seeing your updated question, you cannot do what you want (selecting records before some date) if your column is defined as VARCHAR. You need to store it as a DATETIME value or CAST it, perhaps like this:

SELECT * 
FROM   Work_Order 
WHERE  CAST(WDATE as DATETIME)  < CAST('09/02/2012' as DATETIME)

I don't have SQL SERVER 2005 myself, so the syntax is just a guess.

BellevueBob
  • 9,498
  • 5
  • 29
  • 56
  • 1
    The syntax should be fine, the problem with this approach is that it is culture-sensitive, e.g. in the US it will be using MM/DD/YYYY and in the UK DD/MM/YYYY or so... – Lucero Sep 01 '12 at 18:00
  • Yes; the right-hand side really should be a constant (and not use CAST), but I don't know the proper SQL Server 2005 syntax. Feel free to edit the answer! – BellevueBob Sep 01 '12 at 18:06
  • thanks it works for me on 2008 r2 , will try it with 2005 – Ahmed Kato Sep 01 '12 at 18:16
  • Thanks for the "permission" to edit Bob, but I won't - I don't think that Ahmed cares, and since the question is already scored -4 it's not going to be seen as an important QA resource anyways. – Lucero Sep 01 '12 at 18:43