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
Date format conversion
Moderators: chulett, rschirm, roy
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Got it . Yes , This is just an external string representation of dates.chulett wrote:The fundamental problem here is the fact that dates have no format.
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 ?
Last edited by devsonali on Thu Jun 06, 2013 1:57 pm, edited 1 time in total.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers