DatrTime in SQLSERVER
Moderators: chulett, rschirm, roy
DatrTime in SQLSERVER
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
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
There is no need for a conversion if your datatype is DATE, the display format isn't relevant in this case, just the contents.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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
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
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.
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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
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
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?
Why don't you just try it?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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.
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.
-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:
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.
There is NO FORMAT in the sense of years, months, days, hours, minutes, seconds.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: