Conversion between Date Formats

[Vilen]

Hi. I have a DB in which we store dates in yyyy/mm/dd. However when we
want to display this date via a web frontend, it needs to be in
dd/mm/yyyy. I've declared a function (shown below) which converts
between these date formats and returns a varchar(20). This works fine
however now I need to have the ability to sort on this date field in
the frontend. This requires my function to return a datetime in the
required format. Can this be done?

DECLARE @InputDate nvarchar(20)
DECLARE @OutputDate nvarchar(20)
DECLARE @Day nvarchar(2)
DECLARE @Month nvarchar(2)
DECLARE @Year nvarchar(4)
DECLARE @Time nvarchar(12)

SET @InputDate = '2005/03/01 14:30:00'
SET @Day = cast(datepart(d ay,@InputDate) as nvarchar(2))
SET @Month = cast(datepart(m onth,@InputDate ) as nvarchar(2))
SET @Year = cast(datepart(y ear,@InputDate) as nvarchar(4))
SET @Time = substring(cast( @InputDate as nvarchar(23)),1 2,12)

SET @OutputDate = replicate('0',2-len(@Day)) + @Day + '/' +
replicate('0',2-len(@Month)) + @Month + '/' +
@Year + ' ' + @Time

SELECT @OutputDate AS OutputDate

Thx
Vilen

[David Portas]

Re: Conversion between Date Formats

Return dates as dates and format them for display in the front end or
middle tier. Some users may prefer to format them differently to the
way you do.

In the database dates should be stored as DATETIME or SMALLDATETIME
datatypes. These DO NOT have any fixed format and will always sort
chronologically . It isn't a good idea to sort on a function or
expression if you can avoid it.

--
David Portas
SQL Server MVP
--