T SQL disecting date format in dd/mm/yyyy from datetime parameters

SQL Server 2008
see the code snippet below - from a stored proc. When I pass a date range such as

'01/03/2009'  ,  '2011-06-01'  

The DATEPART assumes the date is in ameican format. What can I do in the stored procedure to read these dates as dd/mm/yyyy regardless of the locale setting etc that may be influencing this behaviour ?
declare @startdate datetime, @enddate datetime 
 set  @startdate =  '01/03/2009' 
 set  @enddate  = '2011-06-01' 
--get dates in to correct format
DECLARE @fromDate char(10)
DECLARE @ToDate char(10)
SET @fromDate = CAST( DATEPART( yyyy, @startdate ) AS CHAR(4))  + '-' + CAST( DATEPART( mm, @startdate ) AS CHAR(2) ) + '-' + 
	CAST( DATEPART( dd, @startdate ) AS CHAR(20))
SET @ToDate = CAST( DATEPART( yyyy, @enddate ) AS CHAR(4))  + '-' + CAST( DATEPART( mm, @enddate ) AS CHAR(2) ) + '-' + 
	CAST( DATEPART( dd, @enddate ) AS CHAR(20))	

select @fromDate, @ToDate, CAST( DATEPART( day, @startdate ) AS CHAR(20))

Open in new window

indikadAsked:
Who is Participating?
 
Anthony PerkinsCommented:
>>What can I do in the stored procedure to read these dates as dd/mm/yyyy regardless of the locale setting etc that may be influencing this behaviour ? <<
Don't use that format.  Instead use an unambiguous date format such as yyyymmdd.
0
 
Dale BurrellDirectorCommented:
0
 
wls3Commented:
Consider using SET LANGUAGE as an alternative to having to manipulate raw strings:

http://teguheko.echodess.com/2007/07/set-locale-in-t-sql-query/
0
 
indikadAuthor Commented:

Hi, acperkins

Your solution is what I need to alleviate my dilemma - where the stored proc can get called by different areas such as applications , Crystal reports etc - so I don’t know the format of the date they send in.
They may send 01/03/2011 and I would not know if they are talking March or January !

So now I have changed the parameter type to char(10) and specified they send dates as dd/mm/yyyy
problem solved.

( although that will not help in my situation. ) - other replies too have good ideas  - so thanks to them too
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.