Sql Server how to insert date into column with date datatype

Question

I am trying to insert birthday date into column date within Sql Server 2008 r2 db I tried to pass it as below but none of them are worked out

INSERT INTO t1 (birthday) VALUES (cast(08/12/2010 as date))

Msg 529, Level 16, State 2, Line 1 Explicit conversion from data type int to date is not allowed.

INSERT INTO t1 (birthday) VALUES (20100812)

Operand type clash: int is incompatible with date

INSERT INTO t1 (birthday) VALUES (20100812)

INSERT INTO t1 (birthday) VALUES ('20100812')

Error converting data type varchar to numeric.

INSERT INTO t1 (birthday) VALUES ('2010-08-12')

Error converting data type varchar to numeric.

If you're passing this data from e.g. [tag:c#], please pass it *as* a `date` parameter. ADO.Net knows how to translate a .NET `DateTime` into an SQL `date` (or `datetime2`, etc) and you can avoid mucking about with strings/formatting entirely. — Damien_The_Unbeliever, Sep 11 '18 at 09:12
Possible duplicate of [Sql query to insert datetime in SQL Server](https://stackoverflow.com/questions/12957635/sql-query-to-insert-datetime-in-sql-server) — AmirNorouzpour, Sep 11 '18 at 09:22
@AmirNorouzpour OF COURSE I searched and tried a lot before posting and tried this answer too but it wont work ! — sam, Sep 11 '18 at 09:23
@Damien_The_Unbeliever ummm yes I am going to pass it from c# later on but for now I am testing the procedure that will be called from c# ... dose that mean that `sql server` will work in different way ?!! — sam, Sep 11 '18 at 09:25
So, stop thinking about SQL from a *strings* perspective. Using parameters has lots of advantages, one of which (here) is that you don't have to pass your data *unnaturally* as a string and then force SQL Server to parse it *back* into the correct type (because it's the conversion to and from strings that *introduces* formatting and parsing issues, so if you can avoid those conversions, you should try to do so) — Damien_The_Unbeliever, Sep 11 '18 at 09:29
@sam the errors say you tried to store dates into a *int* column, not a `date`. `INSERT INTO t1 (birthday) VALUES ('20100812')` works, in fact it's the only format that *isn't* affected by locale. The other will also work if they match the column's collation — Panagiotis Kanavos, Sep 11 '18 at 09:29
@PanagiotisKanavos thank for helpful info I tried but it gives `Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to numeric.` and also I had update my post with screenshot to prove that column is date datatype in the database — sam, Sep 11 '18 at 09:34
@sam are you targeting the wrong table perhaps? The screenshot doesn't say anything, except perhaps that you are looking at the wrong table — Panagiotis Kanavos, Sep 11 '18 at 09:34
@sam post the *actual* table definition command, *actual* INSERT queries. A simple `CREATE #t1 (birthday date); INSERT INTO #t1 (birthday) VALUES ('20100812')` will be enough to reproduce the problem. I'll bet it won't. — Panagiotis Kanavos, Sep 11 '18 at 09:36

Mazhar · Answer 1 · 2018-09-11T09:33:27.803

2

From your edit and error messages generated, it looks like birthday column isn't of DATE datatype

Correct that and this will work

INSERT INTO t1 (birthday) VALUES (cast(08/12/2010 as date))

or

INSERT INTO t1 (birthday) VALUES ('20101210')

Full repo - Working

CREATE TABLE t1 (Birthday DATE)

INSERT INTO dbo.T1 ( Birthday)
VALUES ('20181010') 

INSERT INTO dbo.T1 ( Birthday)
VALUES (CAST('20181010' AS DATE) )

SELECT * FROM dbo.T1

Birthday
2018-10-10
2018-10-10

Error Repo (1) - Enter a Date value into an INT column

CREATE TABLE t1 (Birthday INT)

INSERT INTO dbo.T1 ( Birthday)
VALUES (CAST('20181010' AS DATE) )

Msg 206, Level 16, State 2, Line 7 Operand type clash: date is incompatible with int

Error Repo (2) - Enter a Date value into an NUMERIC column

CREATE TABLE t1 (Birthday NUMERIC)

INSERT INTO dbo.T1 ( Birthday)
VALUES (CAST('20181010' AS DATE) )

Msg 206, Level 16, State 2, Line 7 Operand type clash: date is incompatible with numeric

Error Repo (3) - Enter a INT value into a DATE column

CREATE TABLE t1 (Birthday DATE)

INSERT INTO dbo.T1 ( Birthday)
VALUES (20181010)

Msg 206, Level 16, State 2, Line 6 Operand type clash: int is incompatible with date

edited Sep 11 '18 at 09:33

answered Sep 11 '18 at 09:09

Mazhar

3,797
1
12
29

sorry mistyping error it just an example the exception will be different – sam Sep 11 '18 at 09:11
you can see that I tried them again exception will be different in case the column is not in correct datatype – sam Sep 11 '18 at 09:13
What is the datatype of the column ? Include it in your question – Mazhar Sep 11 '18 at 09:14
column datatype is date – sam Sep 11 '18 at 09:15
@sam `date` works in all SQL Server versions since 2005. `20100812` works. If it didn't, people would have noticed 13 years ago. Post the *actual* table definition and *actual* queries. Post something that can be used to reproduce what you claim. The errors say you tried to store data into a `int`column – Panagiotis Kanavos Sep 11 '18 at 09:31
@sam, the error messages you've posted show that the datatype of the Birthday column is incorrect – Mazhar Sep 11 '18 at 09:34
@Mazhar and the screenshot of the column is date – sam Sep 11 '18 at 09:41
@Mazhar is the collection of the database has effect on that ? – sam Sep 11 '18 at 09:46
As mentioned in earlier comments, if you're certain the datatype is correct then your code is trying to insert into a different table on perhaps a different db – Mazhar Sep 11 '18 at 10:16

score 1 · Answer 2 · answered Sep 11 '18 at 09:15

1

Almost there. Try

INSERT INTO t1 (birthday) VALUES ('2010-08-12')

answered Sep 11 '18 at 09:15

Jorge Aguiar

61
3

Error converting data type varchar to numeric. – sam Sep 11 '18 at 09:16
Can you tell me the datatype of birthday column – Chanukya Sep 11 '18 at 09:18
If that's the error message, then the column datatype isn't date. Please correct your question. – Jorge Aguiar Sep 11 '18 at 09:18
@JorgeAguiar the unseparated format is the only one that isn't affected by the locale.The error though suggest the OP used an int column or targeted the wrong table – Panagiotis Kanavos Sep 11 '18 at 09:33

score 1 · Answer 3 · answered Sep 26 '19 at 22:40

Even though it was asked 1 year back but still for others reference if you use below code it should work: INSERT INTO t1 (birthday) VALUES (convert(date,'08/12/2010'))

Also dates should not be inserted in ddmmyyyy format which is very common mistake.

Prahalad Gaggar · Answer 4 · 2018-09-11T09:13:16.027

0

In the insert, you mentioned two columns i.e. ID and NUM.

Bet you mentioned only 1 values: Hence

INSERT INTO t1 (ID,NUM) VALUES (1, '20100812')

Answer to Modified question:

The data type of column NUM is numeric and you are trying to insert an varchar.

Hence correct your datatype for column NUM or insert into correct column.

edited Sep 11 '18 at 09:13

answered Sep 11 '18 at 09:08

Prahalad Gaggar

11,389
16
53
71

sorry mistyping error it just an example the exception will be different – sam Sep 11 '18 at 09:10
You made a mistake in typing and gave us a -1. WOW! – Prahalad Gaggar Sep 11 '18 at 09:14
@ exception will be different in case the column is not in correct datatype !! – sam Sep 11 '18 at 09:15

Sql Server how to insert date into column with date datatype

4 Answers4