CODEWITHSUNDEEP
sql
Rick Kap
Rick Kap

Reputation: 159

Search date from SQL database from longer than one year ago

I have a SQL Database varchar field which is called date_finish. This field has been setup as a varchar(50). The format of the date is set out like this: 07/06/2017 dd/mm/yyyy.

I'm trying to search the database for all dates over 1 year old using this statement:

SELECT CONVERT(datetime, date_finish, 103) AS DB_DATE, booking_code, cust_id, status
FROM repair_details
WHERE (date_finish > DATEADD(year, - 1, GETDATE()))
ORDER BY DB_DATE

There are some fields that are blank, cust_id is 0 and status aren't complete, so I added:

(date_finish <> '') AND (status = 'COMPLETE') AND (cust_id <> '0')

to the above statement.

In all cases I get an error:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

What am I doing wrong???

Upvotes: 0

Views: 199

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

Convert to a date using an explicit format:

WHERE CONVERT(date, date_finish, 103) > DATEADD(year, - 1, GETDATE())

Obviously, this gets dates since one year ago -- based on your code. If you want older dates, then use < rather than >.

Then, fix the data! You should be storing date/time values using proper types. One method is:

update repair_details
    set date_finish = CONVERT(date, date_finish, 103);  -- sets to default date format on system

alter repair_details alter date_finish date;

Upvotes: 2

FDavidov
FDavidov

Reputation: 3667

The error is because, when the column date_finish appears to be null. As such, you need to have a condition:

 ((date_finish  IS NOT NULL ) AND 
  (date_finish  <> ''       )     ) AND 
 (status        = 'COMPLETE'      ) AND 
 (cust_id       <> '0'            )

Upvotes: 0

cometfish
cometfish

Reputation: 545

You haven't done the date conversion in the WHERE clause - it is comparing a string to a date there.

Upvotes: 0

Related Questions