error

hi i have one varchar field i m convarting that field to datetime in format of dd/mm/yyyy

but i m getting eror

select top 30000 CMS_Upload_Details_ID,Scheme_Code,DrCr,convert(varchar(10),cast(AdditionalField5 as datetime),103) 'ValDate',AdditionalField13 'dept_slip',AdditionalField14 'dept_dt',Cheque_No 'Instrm_No',Amount,CONVERT (varchar(11),Instrm_Date,103)Instrm_Date ,AdditionalField21 'Drawer Name' from Tbl_CMS_UploadDetails with(nolock) where Compare_Status='Pending' and ltrim(rtrim(CMS_Upload_Details_ID)) not in (select top 0 ltrim(rtrim(CMS_Upload_Details_ID)) from Tbl_CMS_UploadDetails with(nolock) where Compare_Status = 'Pending ' and Format_ID =83)and Compare_Status='Pending' and Format_ID =83

error is

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

hi thanx for reply

now i m getting this error

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

hi i tried this

SET DATEFORMAT dmy
select top 30000 CMS_Upload_Details_ID,Scheme_Code,DrCr,Case when IsDate(AdditionalField5)=1 Then Convert(datetime, AdditionalField5,103) Else NULL END as 'ValDate',AdditionalField13 'dept_slip',AdditionalField14 'dept_dt',Cheque_No 'Instrm_No',Amount,CONVERT (varchar(11),Instrm_Date,103)Instrm_Date ,AdditionalField21 'Drawer Name' from Tbl_CMS_UploadDetails with(nolock) where Compare_Status='Pending' and ltrim(rtrim(CMS_Upload_Details_ID)) not in (select top 0 ltrim(rtrim(CMS_Upload_Details_ID)) from Tbl_CMS_UploadDetails with(nolock) where Compare_Status = 'Pending ' and Format_ID =83)and Compare_Status='Pending' and Format_ID =83

now getting error

Server: Msg 241, Level 16, State 1, Line 2
Syntax error converting datetime from character string.

no actuallu it is like this

if @fileFormatId='83'
Begin

set @s='select top '+cast( @j as varchar(10)) +' CMS_Upload_Details_ID,Scheme_Code,DrCr,AdditionalField5 ''ValDate'',AdditionalField13 ''dept_slip'',AdditionalField14 ''dept_dt'',Cheque_No ''Instrm_No'',Amount,CONVERT (varchar(11),Instrm_Date,103)Instrm_Date ,AdditionalField21 ''Drawer Name'' from Tbl_CMS_UploadDetails with(nolock) where Compare_Status=''Pending'' and ltrim(rtrim(CMS_Upload_Details_ID)) not in (select top '+ cast(@i as varchar(10))+' ltrim(rtrim(CMS_Upload_Details_ID)) from Tbl_CMS_UploadDetails with(nolock) where Compare_Status = ''Pending '' and Format_ID ='+ convert(varchar(5),@fileFormatId) +')and Compare_Status=''Pending'' and Format_ID ='+ convert(varchar(5),@fileFormatId)
end

Try the following query..

Hi

Can you just confirm the SQL you are now using for the bold section of your query, and also can we see a sample of the format of the data in AdditionalField5. Also can this field be null (or have non-date text in it) and if so what do you expect to happen in this case.

mani after printing my query i m getting this one ..so i m running this query.

SET DATEFORMAT dmy
select top 30000 CMS_Upload_Details_ID,Scheme_Code,DrCr,Case when IsDate(AdditionalField5)=1 Then Convert(datetime,AdditionalField5,103) Else NULL END as 'ValDate',AdditionalField13 'dept_slip',AdditionalField14 'dept_dt',Cheque_No 'Instrm_No',Amount,CONVERT (varchar(11),Instrm_Date,103)Instrm_Date ,AdditionalField21 'Drawer Name' from Tbl_CMS_UploadDetails with(nolock) where Compare_Status='Pending' and ltrim(rtrim(CMS_Upload_Details_ID)) not in (select top 0 ltrim(rtrim(CMS_Upload_Details_ID)) from Tbl_CMS_UploadDetails with(nolock) where Compare_Status = 'Pending ' and Format_ID =83)and Compare_Status='Pending' and Format_ID =83

AdditionalField5 is varchar field ...and i m getting my date in this format

12/21/2006--this is is mm/dd/yyyy format i want to change this dd/mm/yyyy format