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

No comments: