Convert Many Formats of Date-Time to One Standard Format

Convert many formats of date-time to one standard format

The lubridate package makes quick work of this:

library(lubridate)

d <- parse_date_time(dates, names(guess_formats(dates, c("mdy HM", "mdy IMp"))))
d
## [1] "2014-01-28 18:43:00 UTC" "2014-01-29 16:10:00 UTC"
## [3] "2014-01-30 12:09:00 UTC" "2014-01-30 12:12:00 UTC"
## [5] "2014-02-03 19:49:00 UTC" "2014-02-03 20:03:00 UTC"
## [7] "2014-02-05 14:33:00 UTC"

# put in desired format

format(d, "%m-%d-%Y %H:%M:%S")
## [1] "01-28-2014 18:43:00" "01-29-2014 16:10:00" "01-30-2014 12:09:00"
## [4] "01-30-2014 12:12:00" "02-03-2014 19:49:00" "02-03-2014 20:03:00"
## [7] "02-05-2014 14:33:00"

You'll need to adjust the vector in guess_formats if you come across other format variations.

How to format multiple date formats into single date in python

In an ideal world, you know the format of your inputs.

Where this is not possible, I recommend you use a 3rd party library for mixed format dates.

Two libraries that come to mind are dateutil (via dateutil.parser.parse) and pandas (via pandas.to_datetime). Below is an example implementation with the former.

Note the only occasion when parser.parse was unsuccessful had to be covered with a manual conversion via datetime.strptime. datetime is part of the standard Python library.

from datetime import datetime
from dateutil import parser

list1 = ["30-4-1994", "1994-30-04", "30/04/1994",
         "30-apr-1994", "30/apr/1994","1994-30-apr"]

def converter(lst):
    for i in lst:
        try:
            yield parser.parse(i)
        except ValueError:
            try:
                yield parser.parse(i, dayfirst=True)
            except ValueError:
                try:
                    yield datetime.strptime(i, '%Y-%d-%b')
                except:
                    yield i

res = list(converter(list1))

# [datetime.datetime(1994, 4, 30, 0, 0),
#  datetime.datetime(1994, 4, 30, 0, 0),
#  datetime.datetime(1994, 4, 30, 0, 0),
#  datetime.datetime(1994, 4, 30, 0, 0),
#  datetime.datetime(1994, 4, 30, 0, 0),
#  datetime.datetime(1994, 4, 30, 0, 0)]

You can then format into strings any way you like using datetime.strptime:

res_str = [i.strftime('%d-%m-%Y') for i in res]

# ['30-04-1994',
#  '30-04-1994',
#  '30-04-1994',
#  '30-04-1994',
#  '30-04-1994',
#  '30-04-1994']

How to convert variable with mixed date formats to one format?

You may try parse_date_time in package lubridate which "allows the user to specify several format-orders to handle heterogeneous date-time character representations" using the orders argument. Something like...

library(lubridate)
parse_date_time(x = df$date,
                orders = c("d m y", "d B Y", "m/d/y"),
                locale = "eng")

...should be able to handle most of your formats. Please note that b/B formats are locale sensitive.

Other date-time formats which can be used in orders are listed in the Details section in ?strptime.

How to format date string via multiple formats in python

Try each format and see if it works:

from datetime import datetime

def try_parsing_date(text):
    for fmt in ('%Y-%m-%d', '%d.%m.%Y', '%d/%m/%Y'):
        try:
            return datetime.strptime(text, fmt)
        except ValueError:
            pass
    raise ValueError('no valid date format found')

converting multiple date formats to date in sql server

SQL Fiddle

MS SQL Server 2008 Schema Setup:

create table YourTable(DateCol varchar(20))

insert into YourTable values ('08/10/2013'), ('08102013'), ('081013')

Query 1:

select
  case len(DateCol)
    when 10 then convert(date, DateCol, 103)
    when 8 then convert(date, stuff(stuff(DateCol, 5, 0, '/'), 3, 0, '/'), 103)
    when 6 then convert(date, stuff(stuff(DateCol, 5, 0, '/'), 3, 0, '/'), 3)
  end
from YourTable

Results:

|   COLUMN_0 |
|------------|
| 2013-10-08 |
| 2013-10-08 |
| 2013-10-08 |

R Convert to date from multiple formats

This is pretty much I wrote the anytime package for:

R> dates <- c("01-01-2017","02-01-2017","12-01-2016","20160901","20161001", 
+             "20161101")
R> library(anytime)
R> anydate(dates)
[1] "2017-01-01" "2017-02-01" "2016-12-01" "2016-09-01" 
[5] "2016-10-01" "2016-11-01"
R> 

Parse any sane input reliably and without explicit format or origin or other line noise.

That being said, not starting ISO style with the year is asking for potential trouble, so 02-03-2017 could be February 3 or March 2. I am following the North American convention I too consider somewhat broken -- but is so darn prevalent. Do yourself a favour and try to limit inputs to ISO dates, at least ISO order YYYYMMDD.

---

Related Topics