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.

Dates and Loops question

I have a table called Months_Days and I want to fill it with the months 1 thru 12 in the months column, and fill in the corresponding number of days in the days column. i only want to use 1 loop and 1 Insert Into statement. Here's what i have so far. i can get the months inserted, but it inserts 31 for the number of days for each month. can anybody see what i'm doing wrong?
thanks in advance!

Drop Table Month_Days;
Create Table Month_Days(
Month Number(2),
Days Number(2));

Declare
LoopM Binary_Integer;
LoopD Binary_Integer;
Begin_Date Date;
End_Date Date;
Begin
LoopM:= 0;
LoopD:= 0;

Loop
Begin_Date:= To_Date('01-Jan-2008', 'DD, Mon, YYYY');
End_Date:= Last_Day(Begin_Date);
LoopM:=LoopM+1;
LoopD:=End_Date-Begin_Date+1;
IF LoopM=13 Then
Exit;
End IF;
End_Date:= Add_Months(Begin_Date, 1);
Insert Into Month_Days Values (LoopM, LoopD);
End Loop;
End;Yes: every time you go round the loop you reset Begin_Date to 01-Jan-2008, so you always set LoopD to the number of days in January.

Your code is way over-complicated, and doesn't make use of basic PL/SQL constructs like the FOR loop, e.g.

FOR LoopM IN 1..12 LOOP
...
END LOOP;

Here is a working version of your code:

Declare
LoopD Binary_Integer;
Begin_Date Date;
End_Date Date;
Begin
Begin_Date:= To_Date('01-Jan-2008', 'DD, Mon, YYYY');
FOR LoopM IN 1..12
Loop
End_Date:= Last_Day(Begin_Date);
LoopD:=End_Date-Begin_Date+1;
Insert Into Month_Days Values (LoopM, LoopD);
Begin_Date:= Add_Months(Begin_Date, 1);
End Loop;
End;
/