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.

change your bold area as follow as,

Convert(datetime, AdditionalField5,103) as 'ValDate'

|||

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.

|||

Ok.. Expected one.. Use the following query..

SET DATEFORMAT dmy

Select ....
Case When IsDate(AdditionalField5)=1 Then

Convert(datetime, AdditionalField5,103)

Else NULL END as 'ValDate'

... From ....|||

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.

|||Are you using any UNION on your query..?|||

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..

Code Snippet

SET DATEFORMAT dmy

if @.fileFormatId='83'
Begin

set @.s='select top '+cast( @.j as varchar(10)) +' 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 '+ 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

Exec(@.s)

|||

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 actually i tried this earlier and getting error ..see my 2nd previous post|||

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

|||

I am really wondering....

IsDate() function first parse & validate your input before it parse so there is no issue on your bolded area..

Can you remove other date to character conversions to validate the query..

|||i didn't get u what i have to do now?|||hi yes this field can be null (or have non-date text in it) and so in this case how to d this?