indikad
asked on
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 ?
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))SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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