July 2, 2012 at 1:31 pm
I have a timestamp Column with a Default value of SYSTIMESTAMP.
Since SQL Server's TIMESTAMP Column is not the same what Data Type and Default Constraint should I use in the DDL listed below?
When I load the table from Oracle what type of transformation will I need to perform?
CREATE TABLE QUOTE_DIMENSION
(
QD_QUOTE_IDINT NOT NULL,
QD_QUOTE_END_DATEDATE,
QD_QUOTE_BEGIN_DATEDATE,
QD_STATE_CODEVARCHAR(2),
QD_STATE_DESCRIPTIONVARCHAR(128),
QD_QUOTE_VERSION_DESCRIPTIONVARCHAR(500),
QD_RDB_SYSTEM_GENERATED_FLAGVARCHAR(1),
QD_RDB_ENTRY_DATETIMESTAMP (6) DEFAULT SYSTIMESTAMP,
QD_MARKETING_SOURCEVARCHAR(8)
)
Any help would be greatly appreciated.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 2, 2012 at 2:09 pm
I believe you want to use the datetimeoffset datatype.
http://msdn.microsoft.com/en-us/library/bb630289.aspx
I think your default constraint would just be getdate().
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 2, 2012 at 2:48 pm
Oracle TIMESTAMP can be a maximum of datetime to 9 decimal places;
so depending on your needs, you could go with datetime (which is to 3 decimal places) or datetime2 if your SQL version supports it) which goes to 7 decimal places in SQL Server.
i know there are flavors of the timestamp that also track the date time offset that I think Sean was referring to as well, but i thought they had a different datatype name than timestamp; i'd have to research that more.
Lowell
July 2, 2012 at 2:51 pm
I am by no means well versed in Oracle but my goggle-fu isn't too bad. I used the default constraint of SYSTIMESTAMP on the table and found the Oracle info on that.
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions173.htm
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 2, 2012 at 2:56 pm
ok kewl;
i found a pdf at the microsoft site:
http://www.microsoft.com/en-us/download/details.aspx?id=9483
inside that pdf , way down on page 65, it has a bunch of mapping details, with the datetime stuff starting on page 66.
i was way wrong on the other datatype names for oracle timestamp, i think.
Lowell
July 9, 2012 at 10:11 am
I mentioned using the DataTimeOffset and they did not like that one.
If I have a TimeStamp in Oracle then why I should not use a DATE2 Datatype to load into SQL Server?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 9, 2012 at 11:16 am
Welsh Corgi (7/9/2012)
I mentioned using the DataTimeOffset and they did not like that one.If I have a TimeStamp in Oracle then why I should not use a DATE2 Datatype to load into SQL Server?
You certainly could use that. I had suggested the datetime offset because the default value on the column was SYSTIMESTAMP which include the timezone. If you don't need or care about the timezone portion than either datetime or datetime2 would work fine, that choice would be on how precise do you need it to be.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 9, 2012 at 12:06 pm
Thanks Sean.:-)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply