How to insert Date values in Table in Microsoft SQL Server-CodePudding

I have created this table:

CREATE TABLE HUL 
(
   Company_name varchar(50),
   Purchase_date date,
   Quantity int,
   Price float,
   Age int,
   profit_and_loss float
)

While I am trying to insert date values am getting an error. Am providing the insert query I have tried and the error as well

INSERT INTO HUL (Company_name, Purchase_date, Quantity, Price, Age, profit_and_loss)
VALUES ('Hindusthan_Unilever', '25-03-2022', 1, 1950.40, 125, 669.45);

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

INSERT INTO HUL (Company_name, Purchase_date, Quantity, Price, Age, profit_and_loss)
VALUES ('Hindusthan_Unilever', (CAST('25032022', AS DATE)), 1, 1950.40, 125, 669.45);

Incorrect syntax near the keyword 'AS'.

INSERT INTO HUL (Purchase_date) 
VALUES ('25-03-2022');

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

Can any one please me get the right query to fix this in Microsoft SQL Server.

CodePudding user response：

SQL Server will happily parse strings into dates if they meet a certain set of formats.

Some of those formats depend upon your regional settings. For example, people in some parts of the world (mostly the USA) seem to like dates in month-day-year format, aka MM-dd-yyyy.

In other, slightly more sensible parts of the world people are used to day-month-year format, aka dd-MM-yyyy. This is what you are currently using.

But which of those SQL will accept depends on the regional settings of the system. It can't just accept both, because what would '01-02-2022' mean? Is it the first of Febrary, or the second of January? How can SQL Server know what you meant?

Then there are formats which are unambiguous. They only get interpreted one way no matter what your regional settings are. The simplest example is yearMonthDay, ie, yyyyMMdd. If you provide this format then the interpretation doesn't depend upon your regional settings. The value '20220201' is always interpreted as "the first of February".

So that explains the problem with your first query and your third query. In your second query you have an unnecessary comma. To fix the syntax error...

cast('25032022', AS DATE) -- no good, syntax error
cast('25032022' AS DATE) -- comma removed

But this would be interpreted as the value "22nd day of the 20th month (??) in the year 2503", because you have the numbers the wrong way around.

If necessary ^* you can force SQL to interpret text the way you want using set dateformat. Example:

set dateformat mdy; -- tell sql to read the text as month then day then year
select cast('01-02-2022' as date); -- SQL will now read this as January 2 2022

set dateformat dmy; -- now tell it to read it as day then month then year
select cast('01-02-2022' as date); -- now it's February 1, 2022

You can also "force" a particular interpretation using convert with a style argument, or try_parse with a culture.

^{* "Necssary" here meaning "you might not have the option of changing the incoming format, because developers aren't normally the ones adding data, users are, and you could be receiving the data in a csv, or by some other mechanism over which you have no control, but from which you still need to ingest"}

CodePudding user response：

One thing to understand here is different cultures and languages have different expectations around how dates are formatted.

SQL is no different. It is its own language, and just like any other language it has its own native date format for string literals. When writing SQL, it’s wrong to put your own cultural expectations into this very different language.

So for dates in SQL Server, the correct format is yyyMMdd (no separators). For dates with a time component, there are two accepted formats: yyyyMMdd HH:mm:ss[.fff] and yyyy-MM-ddTHH:mm:ss[.fff]. Anything else is not correct.

Of course, this assumes you're including the the value as a literal in the string. If you're building this string in client code, it is never okay to use string concatenation to include these values in the SQL in the first place. Instead, you must always use parameterized queries/prepared statements.

Done properly, this means a strongly-typed datetime object in whatever client platform you prefer, assigned to a strong-typed DateTime parameter value sent directly to the server, such that the datetime value is never at any point even converted to a string.

https://blogs.msmvps.com/jcoehoorn/blog/2022/07/13/sql-and-dates/

CodePudding user response：

In SQL Server insert into keyword is used to add up some values in a table.

INSERT INTO HUL 
(
    Company_name, 
    Purchase_date,
    Quantity,
    Price,
    Age,
    profit_and_loss
)
VALUES 
(
    'A Worldwide Company',
    '28-07-2022',
    1000,
    175.20,
    35,
    70.50
)