convert date format from mmddyy to yyyy-mm-dd hh:mm:sec

• SQL006

  SSCertifiable

  Points: 5071

  More actions

  August 31, 2015 at 3:06 am

  #320899

  Hi

  I have a date format which i extracted from filename. this date is in format 'mmddyy'

  i want to convert it into 'yyyy-mm-dd hh:mm:sec' format.

  For eg

  Date = 083115 -- mm = 08,dd=31,yy=15

  Result date = 2015-08-31:00:00:00

  time will always be 00:00:00

  i am using SQL server 2012

  thanks

• Louis Hillebrand

  SSCarpal Tunnel

  Points: 4970

  More actions

  August 31, 2015 at 3:19 am

  #1823316

  If the source is always a 6 character string:

  Rearrange the input to a yymmdd string with two substrings.

  Convert to date using CONVERT using style 12 (yymmdd)

  DECLARE @D nvarchar(6) = '083115'

  SELECT CONVERT(DateTime, SUBSTRING(@D, 5, 2) + SUBSTRING(@D, 1, 4), 12)

  Louis.

• SQL006

  SSCertifiable

  Points: 5071

  More actions

  August 31, 2015 at 3:30 am

  #1823318

  Louis Hillebrand (8/31/2015)

  ---

  If the source is always a 6 character string:

  Rearrange the input to a yymmdd string with two substrings.

  Convert to date using CONVERT using style 12 (yymmdd)

  DECLARE @D nvarchar(6) = '083115'

  SELECT CONVERT(DateTime, SUBSTRING(@D, 5, 2) + SUBSTRING(@D, 1, 4), 12)

  Louis.

  Thanks Louis for the solution.

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  August 31, 2015 at 4:10 am

  #1823324

  For fun, here is an alternative

  😎

  USE tempdb;

  GO

  SET NOCOUNT ON;

  DECLARE @STR_DATE VARCHAR(6) = '083115';

  SELECT CONVERT(DATETIME,STUFF(STUFF(@STR_DATE,3,0,'/'),6,0,'/'),1);

  Output

  2015-08-31 00:00:00.000