SQLServer udf: Converting a US date to a UK date manually

CREATE FUNCTION fnConvertDate
(
-- Add the parameters for the function here
@Date varchar(200)
)
RETURNS datetime
AS
BEGIN
-- Declare the return variable here
DECLARE @ReturnDate datetime

DECLARE @ConvertedDate varchar(200)

DECLARE @DD char(2)
DECLARE @MM char(2)
DECLARE @YYYY varchar(10)

-- date day
SET @DD = SUBSTRING(@Date,0,CHARINDEX('/',@Date))
-- date month
SET @MM = SUBSTRING(@Date,CHARINDEX('/',@Date)+1,(CHARINDEX('/', @Date, CHARINDEX('/', @Date)+1)) - CHARINDEX('/',@Date)-1)
-- date year and time
SET @YYYY = SUBSTRING(@Date,(CHARINDEX('/', @Date, CHARINDEX('/', @Date)+2))+1,LEN(@Date))

SET @ConvertedDate = (CAST((@MM +'/' + @DD+ '/' + @YYYY) As datetime))

SET @ReturnDate = (CAST(@ConvertedDate as datetime))

-- Return the result of the function
RETURN @ReturnDate

END
GO

Advertisements

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