on 2011 Dec 19 4:45 PM
Hi,
I have a target table with date field in MMDDYYYY format and it should be sysdate.
When i enter sysdate() function, i am getting it in the format YYYYMMDD. How can i convert sysdate() function to display MMDDYYYY?
Thanks,
Arun
Arun,
"I have a target table with date field in MMDDYYYY format and it should be sysdate." -- what does that mean? Do you have a field with a "date" datatype in your target table, which some application is choosing to show to you in the format "MMDDYYYY"? Or is the target field in question using a string or character datatype (assuming the target table is, in fact, in some DBMS)? In databases, datatypes in the "date" family are stored internally using (typically) a numbering scheme specific to the DBMS; how they appear to you in a given application is not what they "are".
If you're trying to convert the output of the sysdate() function (which returns a datetime datatype) to a string-representing-a-date in the format MMDDYYY, then use the following as your mapping formula:
to_char(sysdate( ),'MMDDYYYY')
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Jeff,
Thank you.
My target field is DATE data type and i am writing the output to SQL SERVER.
I have to get the final output in MMDDYYYY format, but sysdate gives only in YYYYMMDD.
If i use to_char with target field as DATE data type, i am getting NULL, where as if i use target field as VARCHAR, i get it correctly. But client need the output in DATE data type format.
Arun
In SQL Server, do you have a date or a datetime datatype?
If you 1) have a datetime datatype in SQL Server and 2) a datetime datatype in DS, and 3) you populated the DS field with sysdate(), I'd be kinda surprised if that didn't "just work."
Re: " i get in YYYYMMDD format with DATE data type" -- assuming you're talking about "getting" this in DS, with a DATE datatype in DS: how are you seeing that that's what you've "got"? You running it in debug mode and looking at the result of the field with the sysdate() mapping? Sending it to a template table?
Hello
First things first, a date column stores a date (which has no format, its a date) and a varchar can be used to store the string representation of a date. I assume yours is the latter. If so, use the to_char function to convert the date to a string representation of the date before writing to the database. Data Services will automatically convert it using the environment default format if you try and write a date to a string column (check your warnings).
Very easy to implement a specific string representation of a date
to_char(sysdate(), 'MMDDYYYY')
However, if you are constructing a SQL string to write the data things can be more complex, in this case give us more information on your specific environment.
Michael
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Michael,
I am not using sql string. In the target file, i have to add one column (data type is date) with sysdate in MMDDYYYY format.
Like you suggested i did to_char function in the mapping, but target data type being date the output displays null value.
If i change the data type to Varchar, it is working perfectly.
CLient needs in date datatype format.
Thanks,
Arun
User | Count |
---|---|
66 | |
10 | |
10 | |
10 | |
10 | |
8 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.