Conversion failed when converting date and/or time from character string while inserting datetime

You can try this code

select (Convert(Date, '2018-04-01'))

I had this issue when trying to concatenate `getdate()` into a string that I was inserting into an nvarchar field.

I did some casting to get around it:

INSERT INTO [SYSTEM_TABLE] ([SYSTEM_PROP_TAG],[SYSTEM_PROP_VAL]) VALUES
(
'EMAIL_HEADER',
'<h2>111 Any St.<br />Anywhere, ST 11111</h2><br />' +
CAST(CAST(getdate() AS datetime2) AS nvarchar) +
'<br /><br /><br />'
)

That's a sanitized example. The key portion of that is:

`...' + CAST(CAST(getdate() AS datetime2) AS nvarchar) + '...`

Casted the date as `datetime2`, then as `nvarchar` to concatenate it.

This is how to easily convert from an ISO string to a SQL-Server `datetime`:

INSERT INTO time_data (ImportateDateTime) VALUES (CAST(CONVERT(datetimeoffset,'2019-09-13 22:06:26.527000') AS datetime))

Source

[To see links please register here]

For me this worked:

```
INSERT INTO [MyTable]
([ValidFrom]
,[ValidTo])
VALUES
('2020-01-27 14:54:11.000'
,'2023-01-27 14:52:50.000')
```

While writing your SQL insert query, write your date in single quotes and use forward slash in date format (DD/MM/YYYY)
like this:

```sql
insert into table_name (Emp_Id, Name, **DOB**) values
(01, 'Suresh Kumawat', **'22/03/2015'**);
```

This error is also displayed when the date doesn't exist (e.g., the date `'09-31-2021'` doesn't exist, because September has a length of 30 days).

For me with EF Core I needed to use OnModelCreating

For my Job Table , CreatedOn smalldatetime field.

modelBuilder.Entity<Job>().Property(x => x.CreatedOn).HasColumnType("datetime")

I Tried this and it works for me:

SELECT CONVERT(date, yourDate ,104)

You can also get this message:

[![enter image description here][1]][1]


[1]:

when your column is `varchar` or `char` with `'NULL'` values and you are trying to convert your column to `date`.

#### if you use dotnet6 you can validate like this.

```
someDate = Util.SystemDate.GetValidatedDateTime(someDate);

```

and then you can use in SQL.