Convert a text date to unix date format for importing to DB

This occurs in my raw data: '8/31/2009 12:00:00 AM'

I want simply 2009-08-31 and ignore the time part

In my text editor I can use grep find-and-replace to 2009-8-31:
(\d\d?)/(\d\d?)/(\d\d\d\d) 12:00:00 AM,
\3-\2-\1
but how do I get a leading zero to occur when needed. Maybe this calls for sed?
timprotechAsked:
Who is Participating?
 
marchentCommented:
if you want year, month, day format,
$date = '8/31/2009 12:00:00 AM';
$date =~ s/^(\d+)\/(\d+)\/(\d+).*/sprintf("%04d",$3)."-".sprintf("%02d",$1)."-".sprintf("%02d",$2)/e;
print "$date\n";

Open in new window

0
 
hampus_bCommented:
from the zones i figure you are trying to work with a regexp in a database.

take a look at this post: http://www.timflight.com/mysql-update-query-to-convert-date-formats-mmddyyyy-to-yyyy-mm-dd/#comment-26788
0
 
dcesariCommented:
It is not clear from your post whether you have to do the conversion within a bash shell script or something else; if this is the case (bash/ksh), then an efficient way to extract the date from your initial format and reformat it with the necessary leding zeroes is shown in the attached script, it uses only shell builtins ${var%}, ${var#} and printf.


#!/bin/bash
 
raw_data='8/31/2009 12:00:00 AM'
 
month=${raw_data%%/*} # remove longest occurrence of /*
raw_data=${raw_data#$month/} # remove $month/ from $raw_data
day=${raw_data%%/*} # remove longest occurrence of /*
raw_data=${raw_data#$day/} # remove $day/ from $raw_data
year=${raw_data%% *} # remove longest occurrence of /*
 
# output with the desired format
printf "%04d-%02d-%02d\n" $year $month $day

Open in new window

0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
marchentCommented:
Perl,
$date = '8/3/2009 12:00:00 AM';
$date =~ s/^(\d+)\/(\d+)\/(\d+).*/sprintf("%04d",$3)."-".sprintf("%02d",$2)."-".sprintf("%02d",$1)/e;
print "$date\n";

Open in new window

0
 
woolmilkporcCommented:
If you have GNU date (what is your platform?) simply do -
date -d "8/31/2009 12:00:00 AM" +"%Y-%m-%d"
That's all
wmp
 
0
 
timprotechAuthor Commented:
To be clearer, I think this is more of a sed/awk problem to the whole text file before importing into the DB. Note: it has to take into account the single digit months and days, padding them with zeroes.

From this:

d9043f33,Globally Harmonized System - 2009 Status and Strategies,8/31/2009 12:00:00 AM,2:35 pm,3:15 pm,"Mr Daniel Franks, CHMM",2009 National Conference
030da9df,How to Succeed in the Environmental Business Without Really Trying.,8/31/2009 12:00:00 AM,2:35 pm,3:15 pm,"Mr Matthew J Cochise, CHMM",2009 National Conference
a9e0dace,MSDS Management 101,8/31/2009 12:00:00 AM,11:05 am,11:45 am,Joe Miller,2009 National Conference

To this (see 3rd comma-separated field):

d9043f33,Globally Harmonized System - 2009 Status and Strategies,2009-08-31,2:35 pm,3:15 pm,"Mr Daniel Franks, CHMM",2009 National Conference
030da9df,How to Succeed in the Environmental Business Without Really Trying.,2009-08-31,2:35 pm,3:15 pm,"Mr Matthew J Cochise, CHMM",2009 National Conference
a9e0dace,MSDS Management 101,2009-08-31,11:05 am,11:45 am,Joe Miller,2009 National Conference

0
 
woolmilkporcCommented:
GNU date does padding!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.