Set and Declare - passing a column content -

Question

I have seen the following statement online which solves 80% of my problem, but I need to pass a column through this process and not just setting a standard date as shown below (SET @Date = '25032014';).

DECLARE @Date VARCHAR(8); -- input parameter
SET @Date = '25032014';

DECLARE @Date2 CHAR(10);
SET @Date2 = STUFF(STUFF(@Date, 3, 0, '/'), 6, 0, '/');

SELECT @Date2, CONVERT(DATE, @Date2, 103); -- 103 = dd/mm/yyyy
-- 25/03/2014     2014-03-25

would appreciate the answer, as I am going mad here - I am sure an easy one to answer

Answer 1

You can convert that to a select statement, assuming a table MyTable and the column you want to operate on MyDate

DECLARE @Date2 CHAR(10);
SET @Date2 = STUFF(STUFF(@Date, 3, 0, '/'), 6, 0, '/');

would become

SELECT STUFF(STUFF(MYDate, 3, 0, '/'), 6, 0, '/') AS date2 
FROM MyTable

The last query could be written using a subquery

SELECT date2, CONVERT(DATE, date2, 103) from (
  SELECT STUFF(STUFF(MYDate, 3, 0, '/'), 6, 0, '/') AS date2 
  FROM MyTable
) as tbl