Date Conversion in SQL Server?

Question

I have a query that generates this table:

DateCompleted	GtrReference	SourceWarehouse	TargetWarehouse	DateCreated	EntryType	ControlAccount	InitialValue	Operator	FirstDesc	SecondDesc
202302	01062023	W	01	2023-01-06 00:00:00.000	W	1610	6080.00	CWHITE	IN TRANSIT WAREHOUSE	MAIN WAREHOUSE
202302	01172023	W	01	2023-01-17 00:00:00.000	W	1610	6210.00	CWHITE	IN TRANSIT WAREHOUSE	MAIN WAREHOUSE
202302	02022023	W	01	2023-02-02 00:00:00.000	W	1610	3616.00	CWHITE	IN TRANSIT WAREHOUSE	MAIN WAREHOUSE

The Query:

select
DateCompleted,
GtrReference,
SourceWarehouse,
TargetWarehouse,
DateCreated,
EntryType,
ControlAccount,
InitialValue,
Operator,
FirstDesc,
SecondDesc
from
(
    select
    concat(d.TransfCompYear, case when len(d.TransfCompPeriod) = 1 then '0' end, d.TransfCompPeriod) as DateCompleted,
    a.Complete,
    d.Line,
  d.TransfCompPeriod as DateMonth,
  d.TransfCompYear as DateYear,
  a.GtrReference as GtrReference,
  a.SourceWarehouse as SourceWarehouse, 
  max(a.TargetWarehouse) over (partition by a.GtrReference) as TargetWarehouse, 
  a.DateCreated as DateCreated,
  COALESCE(d.ExpectedDueDate, d.ExpectedDueDate) as ExpectedDueDate,
  a.EntryType as EntryType, 
  a.ControlAccount as ControlAccount, 
  max(a.InitialValue) over (partition by a.GtrReference) as InitialValue,
  a.Operator as Operator,
  b.Description as FirstDesc, 
  c.Description as SecondDesc
FROM 
    [GtrMaster] a  
    LEFT JOIN [InvWhControl] b  
     ON (a.SourceWarehouse = b.Warehouse) 
    LEFT JOIN [InvWhControl] c  
     ON (a.TargetWarehouse = c.Warehouse) 
    LEFT JOIN [GtrDetail] d  
     ON (a.GtrReference = d.GtrReference) 
) as i
    WHERE ( i.EntryType = 'W' OR i.EntryType = 'S' ) 
     AND i.Complete = 'Y' AND i.GtrReference >= '' 
     and i.Line = '1'
     and i.DateCompleted >= convert(varchar(6),getdate()-90,112)
    ORDER BY i.DateCompleted desc

What i'm trying to do is change the DateCompleted column to show an actual date.

Ex, I want to change 202302 to show 2023-02-01, and 202301 to show 2023-01-01. How can I convert my current date column to that?

Answer 1

Use Convert:

Convert(DateTime, [DateCompleted] + '01') As TrueDateCompleted

The returned date values you can format as you like.