Internal Date format to SQL Server Date format.

koolsun85 · Post by **koolsun85** » Fri Nov 11, 2011 3:39 pm

Hello All,

I am trying to convert the internal format to SQL Server Date format. It is unable to convert and inserting NULLS - which i cannot.

Help Appreciated.

chulett · Post by **chulett** » Fri Nov 11, 2011 10:26 pm

Where are you getting this "internal format" from and how exactly are you attempting to convert it? Post the syntax you are using.

koolsun85 · Post by **koolsun85** » Fri Nov 11, 2011 11:40 pm

I have used ICONV(input.date,"D-YMD[4,2,2]") to convert it to internal format. this output number has to be inserted into sql server.

chulett · Post by **chulett** » Sat Nov 12, 2011 8:24 am

So... you are successfully converting a date string in YYYY-MM-DD format to an internal date using what you posted, yes? And now you need to insert the result into SQL Server. What is the datatype of your target database column - DATETIME, SMALLDATETIME, something else? What is the "SQL Server date format" that you need to use for that?

Whatever your target datatype is, try search here for it as there have been quite a number of people who have asked the same questions. Best to restrict your search to the Server forum, or at least posts marked with a Job Type of Server.

koolsun85 · Post by **koolsun85** » Tue Nov 15, 2011 7:17 am

Yes.. I could convert .. My target data type is date.

chulett · Post by **chulett** » Tue Nov 15, 2011 8:23 am

I don't "do" SQL Server, so don't know what it expects. How would you do this outside of DataStage? What syntax would you use or what format would the date need to be in to allow it to insert successfully into that field?

Anyone who works with SQL Server care to chime in?

suse_dk · Post by **suse_dk** » Tue Nov 15, 2011 9:26 am

If you have managed to convert your input string correct to a ds date type (yyyy-mm-dd) then you should be able to insert this into a sql server date column.
Could you please poste an example of the input string for the transform?

koolsun85 · Post by **koolsun85** » Tue Nov 15, 2011 10:11 am

I just mapped the internal ds format to sql server and checked whether it works or not- it worked with out any issues..

My Source is YYYY-MM-DD, for all my transformation i have to convert it to internal ds format and while loading into sql server ( Date - datatype) .. just map the internal ds format to the ouput date... it worked for me.

Thanks for the initial help.