|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Syntax error converting datetime from character string.cannot seem to find a solution for. Table: The table is an Employee Health Table. The field Last_PPD is a char type because the Nurses would like to put NA, or NC, or None in the field. Objective: I need to convert Last_PPD into a date type for all dates because I need to do a dateAdd to the result. Problem: I seem unable to convert the string into data so I can do the DateAdd calculation. Code: Select UserID, Last_PPD from tblEmpHealth where UserID IN (Select UserID from tblEmphealth where Last_PPD <> 'NA' AND Last_PPD <> 'NONE' AND Last_PPD <> 'NC' AND Last_PPD<> '') and DATEADD(day,365,convert(datetime,Last_PPD,101))< GetDate() ORDER BY USERID Other comments: The Nested Select does return only dates. The dates are formatted as '1/12/2004' or '12/2/2003'. The SQL server has a regional setting of M/D/YYYY. I set my PC to match that setting. I hope that covers all information you need to help. I have searched everywhere, and the code seems like it should work. So there must be something small I'm missing. Btw, the subject is the error I am receiving. Thank you, Trina Try adding this clause:
WHERE ISDATE(Last_PPD) = 1 Without the limiting clause, it doesn't matter if the inner query returns only dates... you cannot control in which order SQL will parse/convert different parts of the clause. Show quote "GamingAngel" <tsch***@hotmail.com> wrote in message news:1133485173.570816.94870@g44g2000cwa.googlegroups.com... > Hi! I am new to this group, but I am running into a problem that I > cannot seem to find a solution for. > Table: > The table is an Employee Health Table. The field Last_PPD is a char > type because the Nurses would like to put NA, or NC, or None in the > field. > > Objective: > I need to convert Last_PPD into a date type for all dates because I > need to do a dateAdd to the result. > > Problem: > I seem unable to convert the string into data so I can do the DateAdd > calculation. > > Code: > Select UserID, Last_PPD > from tblEmpHealth > where UserID IN > (Select UserID from tblEmphealth where Last_PPD <> 'NA' AND Last_PPD > <> 'NONE' AND Last_PPD <> 'NC' AND Last_PPD<> '') > and DATEADD(day,365,convert(datetime,Last_PPD,101))< GetDate() ORDER > BY USERID > > Other comments: > The Nested Select does return only dates. The dates are formatted as > '1/12/2004' or '12/2/2003'. The SQL server has a regional setting of > M/D/YYYY. I set my PC to match that setting. > > I hope that covers all information you need to help. I have searched > everywhere, and the code seems like it should work. So there must be > something small I'm missing. Btw, the subject is the error I am > receiving. > > Thank you, > Trina > Try adding this clause:
WHERE ISDATE(Last_PPD) = 1 Without the limiting clause, it doesn't matter if the inner query returns only dates... you cannot control in which order SQL will parse/convert different parts of the clause. Show quote "GamingAngel" <tsch***@hotmail.com> wrote in message news:1133485173.570816.94870@g44g2000cwa.googlegroups.com... > Hi! I am new to this group, but I am running into a problem that I > cannot seem to find a solution for. > Table: > The table is an Employee Health Table. The field Last_PPD is a char > type because the Nurses would like to put NA, or NC, or None in the > field. > > Objective: > I need to convert Last_PPD into a date type for all dates because I > need to do a dateAdd to the result. > > Problem: > I seem unable to convert the string into data so I can do the DateAdd > calculation. > > Code: > Select UserID, Last_PPD > from tblEmpHealth > where UserID IN > (Select UserID from tblEmphealth where Last_PPD <> 'NA' AND Last_PPD > <> 'NONE' AND Last_PPD <> 'NC' AND Last_PPD<> '') > and DATEADD(day,365,convert(datetime,Last_PPD,101))< GetDate() ORDER > BY USERID > > Other comments: > The Nested Select does return only dates. The dates are formatted as > '1/12/2004' or '12/2/2003'. The SQL server has a regional setting of > M/D/YYYY. I set my PC to match that setting. > > I hope that covers all information you need to help. I have searched > everywhere, and the code seems like it should work. So there must be > something small I'm missing. Btw, the subject is the error I am > receiving. > > Thank you, > Trina > > The table is an Employee Health Table. The field Last_PPD is a char (My suggestion for this, btw, is to do it right, and add a column that > type because the Nurses would like to put NA, or NC, or None in the > field. stores the reason the date is NULL, instead of allowing the nurses to type whatever they want into what should be a datetime column. Then you can use the right data type and avoid all this comparison and conversion crap.)  
Other interesting topics
|
|||||||||||||||||||||||
