Date/Time: Formatting - input/output -leading zeros?

kristenjberg · October 26, 2012 1:15PM

My input date format is:
1. Omniscope imported my dates (connected to a MySQL table) as text.
2. I put up the field organizer block and told it that my input format is: mddyyyy
3. I need my output format to be mmddyyyy
4. When I tell the field organizer that this is my output format it puts a leading zero in front of EVERY date, not just those that need the leading zero.

Example:
Input 1: 12141964
Output 1: 12141964 IF I tell the field organizer that the output format is mddyyyy

Input 2: 12141964
Output 2: 012141964 IF I tell the field organizer that the output format is mmddyyyy

So, I need Omniscope to recognize: if field length = 8, then do not put leading zero. if field length = 7, put leading zero.

Do I need a formula to get this result? If so, does anyone have a down-and-dirty one I can use?

chris · October 26, 2012 1:20PM

Hi,

Your date format should be MMddyyyy. Please note the uppercase "MM".

kristenjberg · October 26, 2012 1:36PM

Hi Chris -

When I tell Omniscope that the input format is mddyyyy it correctly interprets the source data. But, it gives me fields of different length (7 and 8)

But now, I want to transform the data to add the leading zero, only when needed.

Output format mddyyyy gives me:
10182011 and 1172012

Output format mmddyyyy gives me:
010182011 and 01172012

Output format MMddyyyy gives me:
010182011 and 01172012 (same as above)

Thanks -
~K.

paola · October 26, 2012 2:00PM

Hi - You are correct in saying that your input data is in format mddyyyy, but this is the representation of the format of your data source..probaby MS Excel?

Omniscope is Java-based and uses Java formats where month is represented with capital M characters : M, MM, MMM, or MMMM... differences are explained on our KnowledgeBase page:

http://www.visokio.com/dates-and-times

You will find that (if a date is not automatically correctly imported) you may have to specify the Java input format letter-by-letter, also copying any other characters used, like spaces, /, -, etc. to ensure that Omniscope imports and translates the source date format correctly. You can at the same time also specify an internal Omniscope storage format different from the import format.

kristenjberg · October 26, 2012 2:47PM

Thank you, Paola -
There are no other characters in my source data.

When I did change the output format to that specified by Chris (MMddyyyy) there was no change when I loaded the data into Omniscope. I.e., it continued putting a leading zero in front of already 8 digit dates.

So - I noticed that in MySQL that field is varchar(8). I went into the table and added the leading zero by hand. There were only 50 records.

~K.

	mddyyyy.gif	360K
	MMddyyyy.gif	316K

Welcome!

Categories

Ideas Parade

Tagged