Thursday, March 8, 2012

datetime conversion

hi every one,
can any one help me on datetime conversion prob which might be gone thru by others earlier...

iam using SQL SERVER as db and iam getting the date in format dd/mm/yyyy.but my db uses the date format as mm/dd/yyyy.
i need to return the date values in same format as i get it (i.e dd/mm/yyyy).how should i get out of this prob..

here is sample ex prob which iam going thru..

my table: tdate
create table tdate ( cdate datetime,hcode int )

table values:
6/5/2003, 22
6/7/2003,12
6/20/2003,11
6/22/2003,10
6/24/2003,16
6/26/2003,16
-------
my proc:

CREATE procedure tproc @.sdate datetime , @.edate datetime
as

SELECT convert(VARCHAR,cdate,103)
FROM tdate WHERE convert(VARCHAR,tdate.cdate,101) between convert(VARCHAR,@.sdate,101) and convert(VARCHAR,@.edate,101)
ORDER BY CDATE
GO

iam getting the conversion error.."could not convert char to datetime" when the input parameter exeeds the date 12/06/2003..this format is dd/mm/yyyy

could any one help me in this reg..

thanx in advanceTry to type

SET DATEFORMAT dmy

before calling of the procedure.

No comments:

Post a Comment