Diff date formats in single column

• raghu-935218

  SSC Eights!

  Points: 903

  More actions

  July 1, 2009 at 9:21 am

  #133902

  Hi I have diff formats of date in one coloumn........I need to change them to one format.....

  1)mm/dd/yy 07/01/09

  2)mddyyyy 7012009

  3)mmddyyyy 07012009

  4)yyyymmdd 20090701

  Can you please suggest how to write the query for this.......

  Thanks

• Lowell

  SSC Guru

  Points: 323518

  More actions

  July 1, 2009 at 9:27 am

  #1018506

  i assume you have them stored in a varchar column instead of in a datetime column?

  ideally, everything should be the datetime datatype...

  can you rebuild the table, and set the column to that datatype instead of whatever it is now?

  do a SELECT YOURCOLUMN FROM YOURTABLE WHERE ISDATE(YOURCOLUMN ) = 0

  is there any bad data in it?

  if not you could convert the data no problem...

  SELECT YOURCOLUMN,CONVERT(datetime,YOURCOLUMN) AS CorrectDate FROM YOURTABLE WHERE ISDATE(YOURCOLUMN ) = 1

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Ken McKelvey

  SSCoach

  Points: 18853

  More actions

  July 1, 2009 at 10:57 am

  #1018588

  The following works for the sample data:

  -- needed if not defaulting to American date format

  -- SET DATEFORMAT MDY

  --- *** Test Data ***

  DECLARE @t TABLE

  (

  StringDate varchar(20) NOT NULL

  )

  INSERT INTO @t

  SELECT '07/01/09' UNION ALL

  SELECT '7/01/09' UNION ALL

  SELECT '7012009' UNION ALL

  SELECT '07012009' UNION ALL

  SELECT '20090701'

  --- *** End Test Data ***

  SELECT CAST(StringDate AS datetime) AS DT

  FROM

  (

  SELECT

  CASE

  WHEN ISDATE(StringDate) = 1

  THEN StringDate

  WHEN LEN(StringDate) = 8

  THEN LEFT(StringDate, 2) + '/' + SUBSTRING(StringDate, 3, 2) + '/' + RIGHT(StringDate, 4)

  ELSE LEFT(StringDate, 1) + '/' + SUBSTRING(StringDate, 3, 2) + '/' + RIGHT(StringDate, 4)

  END AS StringDate

  FROM @t

  ) D

  However what happens if you have something like 122009 etc?

  Is it January 2nd 2009 or December 20th 2009?

  The task will be virtually imposible if you have any European (dd-mm-yyyy) dates.

  (You may have other columns indicating who entered the date, or where the date was entered,

  which may enable you to make a rough guess.)

• Lamprey13

  SSCertifiable

  Points: 6210

  More actions

  July 1, 2009 at 11:21 am

  #1018606

  Is the column just a date or does it also include the format (mm/dd/yy)?