Sysdate to date format

[Former Member]

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

[Former Member · Accepted solution]

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')

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

Hmm. Usually SQL Server is pretty tolerant as regards date and date-time handling.

What I'd try is making a datetime field in DS, sticking sysdate() as the map, and sending it in. That doesn't work?

[Former Member · Accepted solution]

If i put sysdate() it works, but i get in YYYYMMDD format with DATE data type. I need it in MMDDYYYY format in DATE data type and not Varchar.

Hope i am not confusing you.

[Former Member · Accepted solution]

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?

[Former Member · Accepted solution]

Thanks Jeff. I converted to DATE to VARCHAR and it is working.

My final output is now in VARCHAR and i guess it won't be a problem.

Arun

[Former Member]

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

[Former Member]

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