sql server: 'Conversion failed when converting date and/or time from character string using'

Question

I have come up with a code from the previous developers about a code wherein the date from the database saved was like

'Jan 1 2018 12:01PM'

Now there is a stored procedure for report output that gets the range of dates,

the datatype is

@datefrom varchar(50),
@dateto varchar(50)

the statement is like this

 where 
(CAST(ai.date_created as DATETIME2) between @datefrom and @dateto) AND
ai.sex = (CASE WHEN @sex = 'ALL' THEN ai.sex ELSE @sex END) AND
ai.special_health_condition = (CASE WHEN @special = 'ALL' THEN ai.special_health_condition ELSE @special END)
END

when code is executed this error shows

Conversion failed when converting date and/or time from character string

i tried to change the format of ai.date_created based on this answer but nothing seems to work. What is wrong here? Sorry, I am a newly graduate student and this came like a puzzle to me. Thanks in advance.

UPDATE

The code goes like this:

    @datefrom date,
    @dateto date,
    @sex varchar(50),
    @special varchar(50)
AS
BEGIN
        select 
    ai.application_no,
    ai.franchise_no,
    ai.operator_name,
    ai.full_address,
    m.motor_no,
    ai.date_created
,ai.frist_name,
ai.middle_name,
ai.surname,
ai.citizenship,
ai.primary_occupation,
ai.contact_no,
ai.email_add,
ai.street,
ai.subdivision,ai.barangay,
ai.city,
ai.sex,
ai.civil_status,
ai.type,
ai.type_of_ownership,
m.chassis_no,
m.year_model,
m.plate_no,
b.brand_name,
ai.tax_certification_issued_on,
ai.tax_certification_no,
m.toda_id
,ai.age,
 ai.special_health_condition

from vfTA_tblApplicationInfo as ai inner join 
vfTA_tblMotor as m on m.motor_id = ai.motor_id inner join
vfTA_tblBrand as b on b.brand_id = m.brand_id 

where 
(CAST(ai.date_created as DATETIME2) between @datefrom and @dateto) AND
ai.sex = (CASE WHEN @sex = 'ALL' THEN ai.sex ELSE @sex END) AND
ai.special_health_condition = (CASE WHEN @special = 'ALL' THEN ai.special_health_condition ELSE @special END)
END

The data that goes are datefrom = 'Jan 1 2018 12:00AM', dateto = 'Jan 31 2018 11:59PM', sex = 'All', special = 'ALL'

This is the code the previous developers left. Thanks in advance.

We'll need some sample data and SQL here that generates the error. Without that, all we can really tell you is that, somewhere, you have a string you're trying to convert to a date, and it's not in a valid format. You shouldn't be storing data types in types that aren't representative of them. Dates should be stored as dates, integers as integers, decimals as... you get the idea. Otherwise you just end up with problems. If you're using SQL Server 2012, or later, or could have a look at `TRY_CAST` and `TRY_CONVERT`; but my recommendation is once you find the problem, is to fix the data types. — Thom A, Feb 21 '18 at 09:04
Is `ai.date_created` a varchar column? If so why? Why are you storing dates as strings?If that column is varchar, it most probably contains values which cannot be converted to date. — Pred, Feb 21 '18 at 09:06
My thoughts exactly. I was also surprised to see the previous developers store dates as varchar. I've tried converting them to date but the error still persists. — John Clarence Castro, Feb 22 '18 at 00:39
Use `SELECT date_created, TRY_CAST(date_created AS DATE) FROM vfTA_tblApplicationInfo` to find the date data that won't convert properly. Then go and fix them all and use the correct datatype — Nick.Mc, Feb 22 '18 at 01:07
@Larnu I have tried the code. Whenever I manually set the date range based from the given situation, the data displays but when I maintain the code, an error 'Incorrect syntax near '1'.' shows. — John Clarence Castro, Feb 22 '18 at 01:08
@Nick.McDermaid I have tried the code based with TRY_CAST. Now a new error shows 'Incorrect syntax near '1'.' but when I manually set the date range from Jan 1 2018 12:00AM - Jan 31 2018 11:59PM leaving only the sex and specialcondition to be filled, the code works perfectly fine — John Clarence Castro, Feb 22 '18 at 01:10
I don't see a `1` in any of your posted code. You need to fix the data first. Have you fixed it? TRY_CAST returns NULL for invalid dates — Nick.Mc, Feb 22 '18 at 01:12
Its difficult to know what the issue is but anyway if you do that you are going to miss out on data. It turns invalid dates into NULLs. I suggest you first find the invalid dates and fix them and it'll probably fix your issue. Then I suggest you convert to the correct datatype — Nick.Mc, Feb 22 '18 at 01:21

John Clarence Castro · Accepted Answer · 2018-02-22T05:21:20.513

3

I have solved the problem

First I changed this:

@datefrom date,
@dateto date

to this:

@datefrom varchar(50),
@dateto varchar(50)

I also changed the CAST to TRY_CAST. The error about 'Incorrect syntax near '1' was due to the datatype date which should be varchar(50). Thanks for the help.

edited Feb 22 '18 at 05:21

answered Feb 22 '18 at 00:43

John Clarence Castro

145
1
2
17

I’m glad you solved your problem but you are just prolonging the issue and most likely getting the incorrect answer if you don’t fix the data type. – Nick.Mc Feb 25 '18 at 13:05
@Nick.McDermaid We are now creating a new model of the database so that we will never come again with the same issues. We found more of this problem. Thanks for the help sir. – John Clarence Castro Feb 26 '18 at 00:49

sql server: 'Conversion failed when converting date and/or time from character string using'

1 Answers1