Total Pageviews

2015/07/17

[MS SQL Server] How to insert an explicit value into datetime column

In Oracle, it would be wrote as bellows:
1
2
3
4
5
6
7
8
INSERT INTO AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE)
VALUES (to_date('01-1月 -14','DD-MON-RR'),
        '2',
        '397000000A',
        '高雄市政府',
        'A00000000A',
        'TEST',
        to_date('20-3月 -15','DD-MON-RR'));


In Microsoft SQL Server, it should be modified as following:
1
2
3
4
5
6
7
8
INSERT INTO AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE)
VALUES (CAST('2014-01-01' AS datetime),
        '2',
        '397000000A',
        '高雄市政府',
        'A00000000A',
        'TEST',
        CAST('2015-03-20' AS datetime));


Reference
[1] http://stackoverflow.com/questions/10262426/sql-server-cannot-insert-an-explicit-value-into-a-timestamp-column

Posted by Albert Kuo at 9:50 AM 0 comments
Labels:

[Oracle to SQL Server Migration] Data Type Mapping for Oracle and MS SQL Server


Oracle data type
SQL Server data type
Alternatives
BFILE
VARBINARY(MAX)
Yes
BLOB
VARBINARY(MAX)
Yes
CHAR([1-2000])
CHAR([1-2000])
Yes
CLOB
VARCHAR(MAX)
Yes
DATE
DATETIME
Yes
FLOAT
FLOAT
No
FLOAT([1-53])
FLOAT([1-53])
No
FLOAT([54-126])
FLOAT
No
INT
NUMERIC(38)
Yes
INTERVAL
DATETIME
Yes
LONG
VARCHAR(MAX)
Yes
LONG RAW
IMAGE
Yes
NCHAR([1-1000])
NCHAR([1-1000])
No
NCLOB
NVARCHAR(MAX)
Yes
NUMBER
FLOAT
Yes
NUMBER([1-38])
NUMERIC([1-38])
No
NUMBER([0-38],[1-38])
NUMERIC([0-38],[1-38])
Yes
NVARCHAR2([1-2000])
NVARCHAR([1-2000])
No
RAW([1-2000])
VARBINARY([1-2000])
No
REAL
FLOAT
No
ROWID
CHAR(18)
No
TIMESTAMP
DATETIME
Yes
TIMESTAMP(0-7)
DATETIME
Yes
TIMESTAMP(8-9)
DATETIME
Yes
TIMESTAMP(0-7) WITH TIME ZONE
VARCHAR(37)
Yes
TIMESTAMP(8-9) WITH TIME ZONE
VARCHAR(37)
No
TIMESTAMP(0-7) WITH LOCAL TIME ZONE
VARCHAR(37)
Yes
TIMESTAMP(8-9) WITH LOCAL TIME ZONE
VARCHAR(37)
No
UROWID
CHAR(18)
No
VARCHAR2([1-4000])
VARCHAR([1-4000])
Yes

The DDL script is for Oracle database:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
CREATE TABLE "AP_NTA"."DBM030FA" 
   ( "STA_DATE" DATE, 
 "ORG_TYPE" VARCHAR2(1 BYTE), 
 "AGE" VARCHAR2(12 BYTE), 
 "AGE_NAME" NVARCHAR2(30), 
 "MASTER_AGE" VARCHAR2(12 BYTE), 
 "USER_ID" VARCHAR2(20 BYTE), 
 "UPDATE_DATE" DATE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DBM_DAT" ;
REM INSERTING into AP_NTA.DBM030FA
SET DEFINE OFF;
Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (to_date('01-1月 -14','DD-MON-RR'),'2','397000000A','高雄市政府','A00000000A','TEST',to_date('20-3月 -15','DD-MON-RR'));
Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (to_date('01-1月 -14','DD-MON-RR'),'1','A00000000A','行政院',null,'TEST',to_date('20-3月 -15','DD-MON-RR'));
Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (to_date('01-1月 -14','DD-MON-RR'),'2','382000000A','新北市政府','A00000000A','TEST',to_date('16-4月 -15','DD-MON-RR'));
Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (to_date('01-1月 -14','DD-MON-RR'),'3','376580000A','新竹市政府','A01000000A ','TEST',to_date('16-4月 -15','DD-MON-RR'));
Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (to_date('01-1月 -14','DD-MON-RR'),'1','A01000000A ','內政部','A00000000A','TEST',to_date('16-4月 -15','DD-MON-RR'));
Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (to_date('01-1月 -14','DD-MON-RR'),'4','376446400A ','竹北市公所','376440000A','TEST',to_date('16-4月 -15','DD-MON-RR'));
Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (to_date('01-1月 -14','DD-MON-RR'),'3','376440000A','新竹縣政府','A01000000A ','TEST',to_date('16-4月 -15','DD-MON-RR'));
--------------------------------------------------------
--  DDL for Index SYS_C0072659
--------------------------------------------------------

  CREATE UNIQUE INDEX "AP_NTA"."SYS_C0072659" ON "AP_NTA"."DBM030FA" ("STA_DATE", "ORG_TYPE", "AGE") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DBM_DAT" ;
--------------------------------------------------------
--  Constraints for Table DBM030FA
--------------------------------------------------------

  ALTER TABLE "AP_NTA"."DBM030FA" ADD PRIMARY KEY ("STA_DATE", "ORG_TYPE", "AGE")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DBM_DAT"  ENABLE;
  ALTER TABLE "AP_NTA"."DBM030FA" MODIFY ("AGE_NAME" NOT NULL ENABLE);
  ALTER TABLE "AP_NTA"."DBM030FA" MODIFY ("AGE" NOT NULL ENABLE);
  ALTER TABLE "AP_NTA"."DBM030FA" MODIFY ("ORG_TYPE" NOT NULL ENABLE);
  ALTER TABLE "AP_NTA"."DBM030FA" MODIFY ("STA_DATE" NOT NULL ENABLE);


The following DDL script had been translated for Microsoft SQL Sever database:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
  CREATE TABLE "AP_NTA"."DBM030FA" 
   ( "STA_DATE" DATETIME, 
 "ORG_TYPE" VARCHAR(1), 
 "AGE" VARCHAR(12), 
 "AGE_NAME" NVARCHAR(30), 
 "MASTER_AGE" VARCHAR(12), 
 "USER_ID" VARCHAR(20), 
 "UPDATE_DATE" DATETIME,
 CONSTRAINT DBM030FA_PK PRIMARY KEY (STA_DATE, ORG_TYPE, AGE)
   ) ;
   

Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (CAST('2014-01-01' AS datetime),'2','397000000A','高雄市政府','A00000000A','TEST',CAST('2015-07-17' AS datetime));
Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (CAST('2014-01-01' AS datetime),'1','A00000000A','行政院',null,'TEST',CAST('2014-07-17' AS datetime));
Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (CAST('2014-01-01' AS datetime),'2','382000000A','新北市政府','A00000000A','TEST',CAST('2014-07-17' AS datetime));
Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (CAST('2014-01-01' AS datetime),'3','376580000A','新竹市政府','A01000000A ','TEST',CAST('2014-07-17' AS datetime));
Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (CAST('2014-01-01' AS datetime),'1','A01000000A ','內政部','A00000000A','TEST',CAST('2014-07-17' AS datetime));
Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (CAST('2014-01-01' AS datetime),'4','376446400A ','竹北市公所','376440000A','TEST',CAST('2014-07-17' AS datetime));
Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (CAST('2014-01-01' AS datetime),'3','376440000A','新竹縣政府','A01000000A ','TEST',CAST('2014-07-17' AS datetime));

Reference
[1] https://msdn.microsoft.com/en-us/library/ms151817.aspx
Posted by Albert Kuo at 9:36 AM 0 comments
Labels: ,
Subscribe to: Posts (Atom)