convert datetime

• trans52

  SSC Enthusiast

  Points: 112

  More actions

  July 29, 2003 at 12:38 pm

  #76034

  Hi guys,

  What is the best way to convert 7/29/2003 8:02:22 AM into this format : 20030729080221 ?

  Corrently i am using :

  SELECT SUBSTRING(replace(replace(replace(convert(varchar,completeDT,120),'-',''),' ',''),':',''),1,14)

  But when it converts i got 1 sec less as in above example

  Thank you

• jpipes

  SSChampion

  Points: 12230

  More actions

  July 29, 2003 at 2:26 pm

  #467592

  Fun one.

  
  
  DECLARE @date DATETIME
  
  SET @date = '7/29/2003 8:02:22 AM'
  
  --
  
  SELECT CAST(CONVERT(CHAR(4), YEAR(@date)) + 
  
  RIGHT('0' + CONVERT(VARCHAR, MONTH(@date)), 2) + 
  
  RIGHT('0' + CONVERT(VARCHAR, DAY(@date)), 2) + 
  
  RIGHT('0' + CONVERT(VARCHAR, DATEPART(hour, @date)), 2) + 
  
  RIGHT('0' + CONVERT(VARCHAR, DATEPART(minute,@date)), 2) + 
  
  RIGHT('0' + CONVERT(VARCHAR, DATEPART(second,@date)), 2) AS VARCHAR(14))
  
  

  Edited by - jpipes on 07/29/2003 2:29:41 PM

• 5409045121009-7368

  SSCoach

  Points: 15740

  More actions

  July 29, 2003 at 2:41 pm

  #467598

  Declare @d Datetime

  Set dateformat mdy

  Select @d='7/29/2003 8:02:22 AM '

  Select @d

  SELECT SUBSTRING(replace(replace(replace(convert(varchar,@d,120),'-',''),' ',''),':',''),1,14)

  ------------------------------------------------------

  2003-07-29 08:02:22.000

  (1 row(s) affected)

  --------------

  20030729080222

  (1 row(s) affected)

  SQL 2000 Service Pack 3