Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jcb267
Constraint Violating Yak Guru
291 Posts |
 Posted - 2009-03-18 : 10:29:48
|
| Can anyone help me with this error:Conversion failed when converting character string to smalldatetime data type.I am trying to run a query and when I put the fy_end_date in the where clause like this:where (hosp_rpt.fy_end_dt > '2008%')I get the error above. Any help would be appreciated. |
|
|
raky
Aged Yak Warrior
767 Posts |
Posted - 2009-03-18 : 10:33:47
|
| Hi,Please post table structure , sample data and expected output |
 |
|
|
jcb267
Constraint Violating Yak Guru
291 Posts |
Posted - 2009-03-18 : 11:00:13
|
quote:
Originally posted by raky
Hi,Please post table structure , sample data and expected output
Here is the whole query:select hosp_rpt.prvdr_num, hosp_nmrc.Itm_val_num, hosp_nmrc.wksht_cd, hosp_nmrc.line_num, hosp_nmrc.Clmn_num,hosp_rpt.fy_end_dtfrom hosp_nmrc INNER JOIN hosp_rpt ON hosp_nmrc.RPT_REC_NUM = hosp_rpt.RPT_REC_NUM where (hosp_nmrc.wksht_cd like 'S300001%'and hosp_nmrc.line_num = '01200' and hosp_nmrc.Clmn_num = '0600')and (hosp_rpt.prvdr_num = 490011 or hosp_rpt.prvdr_num = 490017 or hosp_rpt.prvdr_num = 490069 or hosp_rpt.prvdr_num = 490094 or hosp_rpt.prvdr_num = 490059 or hosp_rpt.prvdr_num = 490136 or hosp_rpt.prvdr_num = 490041 or hosp_rpt.prvdr_num = 493301 or hosp_rpt.prvdr_num = 490032 or hosp_rpt.prvdr_num = 490119 or hosp_rpt.prvdr_num = 490093 or hosp_rpt.prvdr_num = 490046 or hosp_rpt.prvdr_num = 490044 or hosp_rpt.prvdr_num = 490007 or hosp_rpt.prvdr_num = 490057 or hosp_rpt.prvdr_num = 420004 or hosp_rpt.prvdr_num = 490024) and (hosp_rpt.fy_end_dt > '2008%')order by hosp_rpt.prvdr_numThis is what I would like to see for results prvdr_num itm_val_num wksht_cd lin_num clmn_num fy_end_date1 420004 152023 S300001 1200 600 6/30/2008 0:002 420004 154205 S300001 1200 600 6/30/2008 0:003 420004 136249 S300001 1200 600 6/30/2008 0:004 420004 136560 S300001 1200 600 6/30/2008 0:005 420004 141115 S300001 1200 600 6/30/2008 0:00Does that help? |
|
|
|
jcb267
Constraint Violating Yak Guru
291 Posts |
Posted - 2009-03-18 : 11:06:19
|
quote:
Originally posted by jcb267
quote:
Originally posted by raky
Hi,Please post table structure , sample data and expected output
Here is the whole query:select hosp_rpt.prvdr_num, hosp_nmrc.Itm_val_num, hosp_nmrc.wksht_cd, hosp_nmrc.line_num, hosp_nmrc.Clmn_num,hosp_rpt.fy_end_dtfrom hosp_nmrc INNER JOIN hosp_rpt ON hosp_nmrc.RPT_REC_NUM = hosp_rpt.RPT_REC_NUM where (hosp_nmrc.wksht_cd like 'S300001%'and hosp_nmrc.line_num = '01200' and hosp_nmrc.Clmn_num = '0600')and (hosp_rpt.prvdr_num = 490011 or hosp_rpt.prvdr_num = 490017 or hosp_rpt.prvdr_num = 490069 or hosp_rpt.prvdr_num = 490094 or hosp_rpt.prvdr_num = 490059 or hosp_rpt.prvdr_num = 490136 or hosp_rpt.prvdr_num = 490041 or hosp_rpt.prvdr_num = 493301 or hosp_rpt.prvdr_num = 490032 or hosp_rpt.prvdr_num = 490119 or hosp_rpt.prvdr_num = 490093 or hosp_rpt.prvdr_num = 490046 or hosp_rpt.prvdr_num = 490044 or hosp_rpt.prvdr_num = 490007 or hosp_rpt.prvdr_num = 490057 or hosp_rpt.prvdr_num = 420004 or hosp_rpt.prvdr_num = 490024) and (hosp_rpt.fy_end_dt > '2008%')order by hosp_rpt.prvdr_numThis is what I would like to see for results prvdr_num itm_val_num wksht_cd lin_num clmn_num fy_end_date1 420004 152023 S300001 1200 600 6/30/2008 0:002 420004 154205 S300001 1200 600 6/30/2008 0:003 420004 136249 S300001 1200 600 6/30/2008 0:004 420004 136560 S300001 1200 600 6/30/2008 0:005 420004 141115 S300001 1200 600 6/30/2008 0:00Does that help?
I just tried this:select hosp_rpt.prvdr_num, hosp_nmrc.Itm_val_num, hosp_nmrc.wksht_cd, hosp_nmrc.line_num, hosp_nmrc.Clmn_num,hosp_rpt.fy_end_dtfrom hosp_nmrc INNER JOIN hosp_rpt ON hosp_nmrc.RPT_REC_NUM = hosp_rpt.RPT_REC_NUM where (hosp_nmrc.wksht_cd like 'S300001%'and hosp_nmrc.line_num = '01200' and hosp_nmrc.Clmn_num = '0600')and (hosp_rpt.prvdr_num = 490011 or hosp_rpt.prvdr_num = 490017 or hosp_rpt.prvdr_num = 490069 or hosp_rpt.prvdr_num = 490094 or hosp_rpt.prvdr_num = 490059 or hosp_rpt.prvdr_num = 490136 or hosp_rpt.prvdr_num = 490041 or hosp_rpt.prvdr_num = 493301 or hosp_rpt.prvdr_num = 490032 or hosp_rpt.prvdr_num = 490119 or hosp_rpt.prvdr_num = 490093 or hosp_rpt.prvdr_num = 490046 or hosp_rpt.prvdr_num = 490044 or hosp_rpt.prvdr_num = 490007 or hosp_rpt.prvdr_num = 490057 or hosp_rpt.prvdr_num = 420004 or hosp_rpt.prvdr_num = 490024) and (hosp_rpt.fy_end_dt > '2007-12-31')order by hosp_rpt.prvdr_numIs this correct? It did return results but only 3, I would expect to see one result for each prvdr_num specified in the WHERE clause. |
|
|
|
matty
Posting Yak Master
161 Posts |
Posted - 2009-03-19 : 02:10:32
|
You cannot compare a date field with a character.What date range values are you trying to select?quote:
Originally posted by jcb267
Can anyone help me with this error:Conversion failed when converting character string to smalldatetime data type.I am trying to run a query and when I put the fy_end_date in the where clause like this:where (hosp_rpt.fy_end_dt > '2008%')I get the error above. Any help would be appreciated.
|
|
|
|
Jai Krishna
Constraint Violating Yak Guru
333 Posts |
Posted - 2009-03-19 : 05:00:31
|
| where (convert(varchar(55),hosp_rpt.fy_end_dt,111) > '2008%')Jai Krishna |
|
|
|
|
|
|
|
|
Error Msg