how to convert datetime format in SQL Server 2005
datetime data type in database seems to irritate programmers quite often. One of the simple solution is to keep everything in its own state; save datetime in database format and show or get datetime to or from users as they require. SQL Server built-in function convert does both kind of conversions, i.e. datetime to varchar and vice versa. In the following code snippets, I have used “dd/mm/yyyy” datetime style and the code for this one is 103, you can collect code for your chosen datetime style from Date and Time Styles.
CREATE FUNCTION udf_DateTo_Save (@inputDate as varchar(10))
declare @outputDate datetime
select @outputDate = convert(datetime,@inputDate,103)
This user defined function dbo.udp_DateTo_Save takes varchar as input convert it into datetime. Please follow that convert function requires datetime style code as parameter.
CREATE FUNCTION udf_DateTo_Show(@inputDate as datetime)
declare @outputDate varchar(10)
select @outputDate = convert(varchar,@inputDate,103)
dbo.udp_DateTo_Show function does the opposite.
SELECT dbo.udf_DateTo_Save(’01/01/2010′) as DBDate, dbo.udf_DateTo_Show(getdate()) as UIDate
Use those two user defined functions while interacting with database and avoid complexity with datetime.
A Rahim Khan