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

SQL Server returning a CSV from a table

Following up my post SQL Server table valued function to return table of integers from a string I also want to do the reverse sometimes – return a csv from a column of values. I use the method demonstrated here:

USE AdventureWorks
GO
-- Check Table Column
SELECT Name
FROM HumanResources.Shift
GO
-- Get CSV values
SELECT SUBSTRING(
(SELECT ',' + s.Name
FROM HumanResources.Shift s
ORDER BY s.Name
FOR XML PATH('')),2,200000) AS CSV
GO

as the comments point out the only issue here is the use of substring with hardcoded params may trip this up. Someone suggested using stuff() which I have not heard of or tested yet.

As I just found out SQL Server 2000 does not support xml path so the alternative to xml is to use a colaesce to build string of csvs from a table column (taken from here)

DECLARE @EmployeeList varchar(100)

SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') +
CAST(Emp_UniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1

SELECT @EmployeeList