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))
returns datetime
as
BEGIN
  declare
@outputDate datetime
  select
@outputDate = convert(datetime,@inputDate,103)
  return @outputDate
END


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)
returns varchar(10)
as
BEGIN
  declare
@outputDate varchar(10)
  select @outputDate = convert(varchar,@inputDate,103)
  return @outputDate
END


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.

Thanks
A Rahim Khan

Advertisements
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: