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?
