Ad

How Can I Use "Date" Datatype In Sql Server?

- 1 answer

Hello Friends I need your help please when I'm trying to create column in table with data type "Date" it gives me error and I can't add it here is my code

Create table Orders (
Order_ID INT Primary Key,
Book_name varchar(100) ,
isbn varchar(100) ,
Customer_ID INT Foreign key references Customer,
Order_date date,
);

another thing it requires from me to get date of something which is before created one

to be more clear: the query asked indicates to find date before 2 of August How can I do that

I've try to enter data like that

Insert Into Orders(Order_ID, Book_name, isbn, Customer_ID, Order_date) values (1, 'Design User Interface',9345678210123, 1, '02-08-2015'); 
Insert Into Orders(Order_ID, Book_name, isbn, Customer_ID, Order_date) values (2, 'Fire',9654693261489, 1, '05-08-2015'); 
Insert Into Orders(Order_ID, Book_name, isbn, Customer_ID, Order_date) values (3, 'The Odyssey',9654864332511, 2, '01-08-2015'); 
Insert Into Orders(Order_ID, Book_name, isbn, Customer_ID, Order_date) values (4, 'Anatomy',9654877777755, 2, '30-07-2015'); 
Insert Into Orders(Order_ID, Book_name, isbn, Customer_ID, Order_date) values (5, 'Surgery',9654864951753, 2, '01-07-2015'); 

but, it accept only first 3 insertion and last two it gives me error :/

Ad

Answer

You do not have a problem with the "last two".

However, you do have a problem with all of them, but one point after the other.

Literal dates are dependent on your system's culture

Your dates are interpreted as MM-DD-YYYY. This intrepretation is depenent on your system's culture. The first three are turning into wrong dates but work. The 4th breaks and the fifth is never executed (due to the error before).

So the actual error lies on line 4.

Whenever you deal with dates, use culture independent formats. It is better to use either of the following-

universal format

20150730 (=> the 30th of July in 2015)

ODBC-format

{d'2015-07-30'} or {t'23:30:59'} or {ts'2015-07-30 23:30:59'}

ISO 8601

'2015-07-30T00:00:00'
Ad
source: stackoverflow.com
Ad