CODEWITHSUNDEEP
sqlsql-serverstringdatetime
empugandring
empugandring

Reputation: 571

SQL Server convert string to datetime fails

I have data like this :

+-----+---------+----------+
| id  | name    | expdate  |
+-----+---------+----------+
| 1   | chookies| 02/2015  |
| 2   | snack   | 03/2015  |
| 3   | snack1  | 04/2015  |
+-----+---------+----------+

Datatype of expdate is varchar(7) and I want to known name of cookies where expired date from now to 03/2015.

I tried with this, but get an error :

select     
    id, name, 
    convert(datetime, '01/' + expdate, 103) 
from 
    cookies 
where 
    datediff(month, datetime, convert(datetime, '01/' + expdate, 103))

Error:

Conversion failed when converting datetime from character string.

I am using SQL Server 2005, what am I missing?

Can anyone help me?

Thank you

Upvotes: 1

Views: 1115

Answers (2)

Vaibhav J
Vaibhav J

Reputation: 1334

Try:

select 
    id, name, expdate 
from 
    cookies 
where 
    convert(datetime, '01/' + expdate, 103) between getdate() 
                                                and convert(datetime, '01/03/2015', 103)

There is no need to use datediff function, you will just need to check the expdate is between the given period.

Also, you are getting this error because the 2nd parameter in the datediff function should be a valid date not just a string 'datetime'.

Upvotes: 0

Eduard Uta
Eduard Uta

Reputation: 2607

This can be done with a simple between in the where clause like so: 

select id,name,convert(datetime,'01/'+expdate,103) 
from cookies 
where convert(datetime,'01/'+expdate,103) between getdate() and convert(datetime,'2015-03-01 00:00:00.000')

Upvotes: 1

Related Questions