none
Change three different date formats in 1 table

    Question

  • HI

    I am working on an ETL project. One of our table gets three date columns but all of them are in different formats e.g.

    Column1 - dmy

    Column2 - ymd

    COlumn3 - mdy

    I need to transform these columns in sql server default dateformat.

    Can anyone please help me what would be the best way to do it. 

    p.s. the size of table is about 30-35 million rows

    Regards

    Harris

    Friday, March 08, 2013 12:35 PM

Answers

  • Apologies as I don't quite understand the question.  Are  you saying you are getting a date from your source system, but need to store them off in a table in 3 different formats?  Or you are receiving 3 dates (formatted differently) from your source system, and you want to store them in your table in a date field?  I think convert may be what you are after.  For example:

    select [BritishToIso] = convert(date, '08-03-2013', 103) 
          ,[AnsiToIso] = convert(date, '2013-03-08', 102)
          ,[UsToIso] = convert(date, '03-08-2013', 101)

    There are many more date formats you can work with, and conversions to / from different data types.  If this gets you going in the direction you wish, please read more on the convert function here.

    HTH,
    John

    • Marked as answer by grahan007 Tuesday, May 14, 2013 9:26 AM
    Friday, March 08, 2013 1:47 PM
  • As noted above, if you store it in DATE/DATETIME/SMALLDATETIME column, you can translate it to any string date format:

    http://www.sqlusa.com/bestpractices/datetimeconversion/


    Kalman Toth Database & OLAP Architect
    Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    • Marked as answer by grahan007 Tuesday, May 14, 2013 9:26 AM
    Saturday, March 09, 2013 1:01 AM
    Moderator

All replies

  • If they're just those three formats then you can create an extra column, lets say....ALLDATE.

    From this we calculate the correct string date to convert to datetime for all three formats.

    I'm guessing your year is in a four digit format eg (2013) so we'll work with that. And I'm also going to assume your dates are qualified with a "/", if they are "-" you can easily change my code...here goes...

    UPDATE 'TABLE'
    SET ALLDATE = SUBSTRING(DATE,7,4) + '-' + SUBSTRING(DATE,4,2) + '-' + SUBSTRING(DATE,1,2) 
    WHERE SUBSTRING(DATE,3,1) IN ('/')
    AND SUBSTRING(DATE,6,1) IN ('/')
    AND SUBSTRING(DATE,4,2) IN ('01','02','03','04','05','06','07','08','09','10','11','12')
    
    UPDATE 'TABLE'
    SET ALLDATE = SUBSTRING(DATE,1,4) + '-' + SUBSTRING(DATE,6,2) + '-' + SUBSTRING(DATE,8,2)
    WHERE SUBSTRING(DATE,5,1) IN ('/')
    AND SUBSTRING(DATE,7,1) IN ('/')
    
    UPDATE 'TABLE'
    SET ALLDATE = SUBSTRING(DATE,7,4) + '-' + SUBSTRING(DATE,1,2) + '-' + SUBSTRING(DATE,4,2)
    WHERE SUBSTRING(DATE,3,1) IN ('/')
    AND SUBSTRING(DATE,6,1) IN ('/')
    AND (ALLDATE IS NULL OR ALLDATE='')

    Then

    select CAST(ALLDATE as DATETIME)

    from 'table'

    If you've mixed up some months and dates unfortunately they're impossible to determine when stored as strings as it is. So this is a closest solution you'll find.


    Friday, March 08, 2013 12:54 PM
  • Apologies as I don't quite understand the question.  Are  you saying you are getting a date from your source system, but need to store them off in a table in 3 different formats?  Or you are receiving 3 dates (formatted differently) from your source system, and you want to store them in your table in a date field?  I think convert may be what you are after.  For example:

    select [BritishToIso] = convert(date, '08-03-2013', 103) 
          ,[AnsiToIso] = convert(date, '2013-03-08', 102)
          ,[UsToIso] = convert(date, '03-08-2013', 101)

    There are many more date formats you can work with, and conversions to / from different data types.  If this gets you going in the direction you wish, please read more on the convert function here.

    HTH,
    John

    • Marked as answer by grahan007 Tuesday, May 14, 2013 9:26 AM
    Friday, March 08, 2013 1:47 PM
  • Rows are not records. Your mindset is still locked in 1960's COBOL where the fields were text strings for display. In SQL, we have abstract data types, such as DATE. 

    Now build a look up table with a DATE data type column and columns for that date in each format. Do a simple join. fifty or 100 years ought to be more than enough. 

    The worst way to do this is with string functions, row by row. It will not optimize. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Saturday, March 09, 2013 12:54 AM
  • As noted above, if you store it in DATE/DATETIME/SMALLDATETIME column, you can translate it to any string date format:

    http://www.sqlusa.com/bestpractices/datetimeconversion/


    Kalman Toth Database & OLAP Architect
    Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    • Marked as answer by grahan007 Tuesday, May 14, 2013 9:26 AM
    Saturday, March 09, 2013 1:01 AM
    Moderator