Inserting time into SQL Server

Question

Example query:

INSERT INTO Table
VALUES (CAST('13-07-2001' AS DATE))

Ends with

Conversion failed when converting date and/or time from character string.

Swapping month with day fixes the issue, but i would prefer using global system, not American.

Gordon Linoff · Accepted Answer · 2020-04-08 14:52:20Z

1

Use a standard format. For SQL Server, YYYYMMDD always works for dates:

INSERT INTO Table (datecol)
    VALUES (CAST('20010713' AS DATE))

You should list the columns that you are inserting into, as well.

answered Apr 8, 2020 at 14:52

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

forpas · Accepted Answer · 2020-04-08 15:00:18Z

0

In CAST and CONVERT (Transact-SQL) you can find that the style of your dates is 105.
So you can do it like this:

INSERT INTO Table
VALUES (CONVERT(DATE, '13-07-2001', 105))

answered Apr 8, 2020 at 15:00

forpas

165k10 gold badges51 silver badges85 bronze badges