Date format conversion

devsonali · Post by **devsonali** » Wed Jun 05, 2013 3:02 pm

Hello All,
Perhaps this question is very simple but at this point I do not know a way to do it.

I have a string in the format yymmdd and I want to convert it to date format of mm-dd-yy .
I am achieving this by first breaking the string into the required format of mm-dd-yy by using substring and concatenation , and then converting the string into date by stringtodate function .

I was curious if there is direct way to achieve this ?
I went through the documentation of date functions but none discusses a direct way of doing this .

What I am really after is to learn a way to convert date from and to any formats

Thanks for reading

chulett · Post by **chulett** » Wed Jun 05, 2013 10:18 pm

The fundamental problem here is the fact that dates have no format. A date is a date is a DATE and are stored internally as a number, an offset from a zero date. External string representations of dates are what have formats like your mm-dd-yy.

You convert a string in a specific format to a date in a Parallel job with the StringToDate() function. You convert a date into a string with a specific format using DateToString(). Both functions take a mask which specifies the format of the string involved.

ray.wurlod · Post by **ray.wurlod** » Wed Jun 05, 2013 11:40 pm

Why not just convert the original string using a format string of "%yy%mm%dd" (with no delimiters, just like the data)?

devsonali · Post by **devsonali** » Thu Jun 06, 2013 1:55 pm

chulett wrote:The fundamental problem here is the fact that dates have no format.

Got it . Yes , This is just an external string representation of dates.
So StringToDate pretty much converts the string (of given format) into a Date . So when I tested this by using StringToDate(linkname.DateString,"%yy%mm%dd") it converted the input date (say 121031) to 1912-10-31 (which I think is the default date format YYYY-MM-DD) .

Now if I have to simply load this date into oracle table , it might just load fine as Oracle has this date format as well by default.

However , after I ran this test job I have 2 more questions
1 Does the tool always (by default)assumes the year '12' as 1912 ? What the incoming data is referring to 2012.-10-31 ?
2 If I were to load this date to another database table whose default date is in the form of say mm-dd-yy or mm/dd/yyyy . Would my above string to date conversion load these tables without issues ?

devsonali · Post by **devsonali** » Thu Jun 06, 2013 1:56 pm

ray.wurlod wrote:Why not just convert the original string using a format string of "%yy%mm%dd" (with no delimiters, just like the data)?

Ray,
I am sorry , I don't think I understood your question (or suggestion here) ? Can you please elaborate?

ray.wurlod · Post by **ray.wurlod** » Thu Jun 06, 2013 2:34 pm

No elaboration is possible.

You have a string that matches "%yy%mm%dd" so use that format string to change its data type to Date.

chulett · Post by **chulett** » Thu Jun 06, 2013 2:50 pm

He's saying there's no need for your cut/paste rearrangement of the parts, simply convert it to a date "as is".

chulett · Post by **chulett** » Thu Jun 06, 2013 3:02 pm

As to your other questions:

1. No. Look up what the CENTURY PIVOT option controls. Or you may be able to supply the pivot year with the function call, I don't recall but it should all be documented.

2. My rule is to never rely on a "default date format" when loading to a database table. Standardize on a format (say like an ISO timestamp) and then leverage the database's conversion function. For example, in Oracle that would be using TO_DATE() with a format mask that matches the 'standard' string you build. That approach always works regardless of whatever the 'default' date format is or isn't.