Inserting data in TIMESTAMP

Question

I just created a new column SCHEDULE in a table COURSE_OFFERING with the data type TIMESTAMP.

While inserting data into the column (WHERE the primary key is Course_ID) I am using below code:

INSERT INTO COURSE_OFFERING ('SCHEUDLE')
VALUES (TO_TIMESTAMP ('07-JAN-2019 07:00:00','DD-MON-YYYY HH:MI:SS'))
WHERE COURSE_ID = '13477';

But SQL Server responded with an error

Missing SELECT keyword

Can someone guide me where did I go wrong? or what should I use to insert data in timestamp column?

I am very new to the coding world, and is actually just learning, I would appreciate if you can keep it simple.

Possible duplicate of [Difference between datetime and timestamp in sqlserver?](https://stackoverflow.com/questions/7105093/difference-between-datetime-and-timestamp-in-sqlserver) — Dave C, Dec 12 '18 at 18:25
The `timestamp` type is not user insertable, it is used for row versioning. I think you need a `datetime` in your use case. — Dave C, Dec 12 '18 at 18:26
And `TO_TIMESTAMP` is an Oracle function. I believe you've presented us with an [XY Problem](http://xyproblem.info/). Please have a look [here](https://dba.meta.stackexchange.com/questions/2976/help-me-write-this-query-in-sql) and [here](https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/), then edit your question as needed. — Eric Brandt, Dec 12 '18 at 18:47
Your query makes no sense at all. Either database should get an error at the `where` and at the string where the column name should be. — Gordon Linoff, Dec 12 '18 at 20:37

benjamin moskovits · Answer 1 · 2018-12-12T19:43:32.430

0

The question is clearly coming from an Oracle environment but this is how many people do it in SQL Server:

-- where t1 is of type datetime2 and date is mm/dd/yyyy

INSERT INTO #X56 (T1)
SELECT CONVERT(datetime2,'07/01/2019 07:00:00', 103)

edited Dec 12 '18 at 19:43

answered Dec 12 '18 at 19:04

benjamin moskovits

5,261
1
12
22

1

This answer is incorrect. You can't do an INSERT with a WHERE clause. – HardCode Dec 12 '18 at 19:30
@HardCode - um, yes you can, if you're using `INSERT ... SELECT ...`. I cannot think of anything off-hand that is restricted in that `SELECT`. There may be some edge cases but `WHERE` certainly isn't one of them. – Damien_The_Unbeliever Dec 13 '18 at 07:44
@Damien_The_Unbeliever in INSERT ... SELECT, the WHERE is part of the SELECT, not the INSERT, to be technical. You cannot use `INSERT INTO foo (t1) VALUES ('bar') WHERE t2 = 123`, which was the point to ben's answer. Ben has since edited his answer. – HardCode Dec 13 '18 at 15:17
The edit history only shows `SELECT` not `VALUES`. It's possible he managed to get an edit in in the grace period in which case I apologise that I couldn't see it there. But I'd thought the whole point of ben's answer was to switch from `VALUES` to `SELECT`. – Damien_The_Unbeliever Dec 13 '18 at 15:26

score 0 · Answer 2 · answered Dec 13 '18 at 07:36

or what should I use to insert data in timestamp column?

It seem to me that all he needs is UPDATE, as he wants to fill in only this column and only for already existing row (he was trying to use WHERE clause).

So the syntax should be this one:

update COURSE_OFFERING
set SCHEUDLE = '20190107 07:00:00'
where COURSE_ID = '13477';

But the problem is that he came here from the Oracle world (note TO_TIMESTAMP), so the column created has a wrong type. He wanted to add datetime column, but used Oracle data type that has another meaning in SQL Server.

So what he should do now is first drop the wrong column:

alter table COURSE_OFFERING drop column SCHEUDLE;

And then add the correct column:

alter table COURSE_OFFERING add SCHEUDLE datetime;

And only after these 2 steps do an update.

Inserting data in TIMESTAMP

2 Answers2