Month and Day Leading Zeros? 09 not 9

declare @REL_START_DATE as datetime = GetDate()

SELECT cast(year(@REL_START_DATE) as varchar(4)) + '-' + RIGHT('0' + cast(month(@rel_start_date) as varchar(2)),2) + '-' + right('0' + cast(day(@rel_start_date) as varchar(2)),2) + ' 00:00:00.000' as 'REL_START_DATE'

DateTime into Date

, CONVERT(char(10), DateOfHire,126)


turns DateTime in to Date