DatrTime in SQLSERVER

[cnu]

Hi:

I am gettting the date from source oracle as mm/dd/yyyy.
I need a same format in the target also.
In the target SQLServer we have Datetime.

Please let me know the conversion syntax in the transformer.

Thanks
cnu

[ArndW]

There is no need for a conversion if your datatype is DATE, the display format isn't relevant in this case, just the contents.

[cnu]

Thanks !!
Target is Sqlserver.So date is coming from Oracle source i form of mm/dd/yyyy.I need a same format in the target.
In the SQL server DDL is Datetime.
Can i map the field from date to datetime in the transformer.
In SQL Server database datatype is Datetime available,date is not there.
So can i use datetime to get the mm/dd/yyyy format.
Datetime means we should provide date and time right?
Can you please advice me.

Thanks
cnu

[ArndW]

The Date and Time value are not stored in any "format" at all, the format is a display-only attribute.

What have you actually tried so far? Oracle DATEs contain time information, if you read this as a timestamp and declare your output as timestamp as well this could work.

[cnu]

Thanks for quick reply !!

I am getting date from oracle database as mm/dd/yyyy format.
Datatype is Date in Oracle source.

In the target Datatype is datetime.I need a same format mm/dd/yyyy in the target SQL Server also.

If i map the date to Datetime field in the Transformer,can i get the mm/dd/yyyy.

datetime means it will expect time also in SQLserver target right?

Please advice me.

Thanks
cnu

[ArndW]

cnu - I give up! As mentioned many times before, a date or timestamp column has no format and does NOT need conversion. The only time you have a format is when this column is converted to a display or other format!
Why don't you just try it?

[chulett]

You are missing Arnd's rather fundamental point that dates in a relational database are not stored with any "format" at all. This talk about "mm/dd/yyyy" is only applicable on output, the external format, what you want the date to look like after you pull it from the database. So all this "I need a same format in SQL Server" is incorrect.

Have you actually tried Arnd's suggestion? Why don't we do that and see what happens. Declare the Oracle DATE field as a Timestamp (length 19) in the job, do the same on the SQL Server side and pass it through unmolested. Post the results.

[chulett]

Dang, too slow.

[cnu]

Hi guys:

I am asking about Datetime datatype format in SQL Server.
Is it only date or date and time.
Can you give me the format of datetime in the sqlserver.

Thanks
cnu

[ray.wurlod]

Very well. It's a binary number, representing the interval between some arbitrary (but constant) zero and the date/time in question.

There is NO FORMAT in the sense of years, months, days, hours, minutes, seconds.

[chulett]

I am asking about Datetime datatype format in SQL Server. Is it only date or date and time.

Here I am thinking that question really should answer itself.

[cnu]

Thanks Ray !!
I want a same source date format in the target also,but there is no date datatype in the target SQL Server, only datetime is there.

Is there any datatype is available for only date.We can change the metadata.

Please advice me how can i get only date same as souce to the target.

If I have to map date to datetime,how can get the only date.

Thanks in advance
cnu

[ray.wurlod]

There is only DATETIME. It should accept regular date formats without a time component (try that). If not, supply 00:00:00 as the time component.

[cnu]

Thanks Ray,i will try for that.

Thanks
cnu