create database QLTV go use QLTV go create table docgia( madg char (10) primary key,…
- User
create database QLTV go use QLTV go
create table docgia( madg char (10) primary key, tenp nvarchar(20)not null, diachi char (20), sdt char(20) , ); go create table DMS( mas char(10) primary key , tens nvarchar(20), tentg nvarchar(20), NXB nvarchar(20), namxb datetime ,
); go create table TTMT( mas char(10), madg char (10), ngaym datetime, ngayht datetime, ngayt datetime, constraint n1 primary key(mas,madg), constraint n2 foreign key(mas) references DMS(mas), constraint n3 foreign key(madg) references docgia(madg), ); go /* câu 2*/ insert into docgia values ('mdg1',N'trần công hoàn','thái nguyên',0979447653), ('mdg2',N'trần công ','cao nguyên',0979447653), ('mdg3',N'trần hoàn','thái bình',0979447653), ('mdg4',N'công hoàn','bắc ninh ',0979447653), ('mdg5',N'trần thị linh','hà nội',0979447653); select * from docgia insert into DMS values ('s01',N'bách khoa ',N'trần công anh',N'minh anh ','2023'), ('s02',N'công nghệ ',N'trần anh',N'hoàng anh ','2022'), ('s03',N'tình yêu ',N'công anh',N'tuan an ','2021'), ('s04',N'cách làm hack',N'hoàn anh',N'tuan binh ','2011'), ('s05',N'cách lừa người ',N'linh anh',N'hoàng hoan','2012'); select * from DMS insert into TTMT values ('s01',N'mdg1','1/2/2020','1/1/2021','1/1/2021'), ('s02',N'mdg2','2/7/2010','1/1/2021','1/1/2022'), ('s03',N'mdg3','1/7/1090','1/1/2021','1/1/2023'), ('s04',N'mdg4','2/2/2012','1/1/2021','1/1/2022'), ('s05',N'mdg5','1/7/2015','1/1/2021','1/1/2022'); select * from TTMT
- Assistant
The SQL script you provided is attempting to create a database named
QLTVand create three tables:docgia,DMS, andTTMT. It then inserts some sample data into these tables. Below is a refined version of your script with some corrections:- Removed unnecessary commas at the end of the
CREATE TABLEcolumn definitions. - Changed the data type
datetimetointfornamxbinDMSfor year storage (if you intend to store only the year as a number). - Corrected date formats for compatibility and clarity.
Here's the updated SQL script:
-- Create Database CREATE DATABASE QLTV; GO -- Use Database USE QLTV; GO -- Create 'docgia' Table CREATE TABLE docgia ( madg CHAR(10) PRIMARY KEY, tenp NVARCHAR(20) NOT NULL, diachi NVARCHAR(20), sdt CHAR(20) ); GO -- Create 'DMS' Table CREATE TABLE DMS ( mas CHAR(10) PRIMARY KEY, tens NVARCHAR(20), tentg NVARCHAR(20), NXB NVARCHAR(20), namxb INT -- Change to INT for the year, or keep datetime if full date is preferred ); GO -- Create 'TTMT' Table CREATE TABLE TTMT ( mas CHAR(10), madg CHAR(10), ngaym DATETIME, ngayht DATETIME, ngayt DATETIME, CONSTRAINT n1 PRIMARY KEY(mas, madg), CONSTRAINT n2 FOREIGN KEY(mas) REFERENCES DMS(mas), CONSTRAINT n3 FOREIGN KEY(madg) REFERENCES docgia(madg) ); GO -- Insert Data into 'docgia' INSERT INTO docgia (madg, tenp, diachi, sdt) VALUES ('mdg1', N'trần công hoàn', 'thái nguyên', '0979447653'), ('mdg2', N'trần công', 'cao nguyên', '0979447653'), ('mdg3', N'trần hoàn', 'thái bình', '0979447653'), ('mdg4', N'công hoàn', 'bắc ninh', '0979447653'), ('mdg5', N'trần thị linh', 'hà nội', '0979447653'); SELECT * FROM docgia; -- Insert Data into 'DMS' INSERT INTO DMS (mas, tens, tentg, NXB, namxb) VALUES ('s01', N'bách khoa', N'trần công anh', N'minh anh', 2023), ('s02', N'công nghệ', N'trần anh', N'hoàng anh', 2022), ('s03', N'tình yêu', N'công anh', N'tuan an', 2021), ('s04', N'cách làm hack', N'hoàn anh', N'tuan binh', 2011), ('s05', N'cách lừa người', N'linh anh', N'hoàng hoan', 2012); SELECT * FROM DMS; -- Insert Data into 'TTMT' INSERT INTO TTMT (mas, madg, ngaym, ngayht, ngayt) VALUES ('s01', 'mdg1', '2020-02-01', '2021-01-01', '2021-01-01'), ('s02', 'mdg2', '2010-07-02', '2021-01-01', '2022-01-01'), ('s03', 'mdg3', '1990-07-01', '2021-01-01', '2023-01-01'), ('s04', 'mdg4', '2012-02-02', '2021-01-01', '2022-01-01'), ('s05', 'mdg5', '2015-07-01', '2021-01-01', '2022-01-01'); SELECT * FROM TTMT;Key Changes:
- Removed extra commas at the end of column definitions in
CREATE TABLEstatements. - Used the
INTdata type fornamxbin theDMStable to store just the year. - Used ISO date format ('YYYY-MM-DD') for date literals to ensure unambiguous parsing.
- Added column names explicitly in the
INSERTstatements for better clarity and maintainability.
Ensure that your SQL server's settings allow the execution of this script (e.g., permissions to create databases). Adjust the data types and logic according to your precise requirements (e.g., exact datatype for year if more than just year is needed).
- Removed unnecessary commas at the end of the
- User
sửa lỗi này của đoạn code trên .Msg 242, Level 16, State 3, Line 52 The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated.
- Assistant
The error message you're getting indicates an issue with converting a string to a
datetimevalue. This typically happens if the string is not in a recognized date format. The SQL Server expects thedatetimevalues to be in a specific format, which is typicallyYYYY-MM-DD(ISO 8601 format), but depending on the server settings, other formats may be expected.Looking at the code you provided, the
INSERTstatements into theTTMTtable attempt to use dates in a format that might not be recognized, especially if regional settings are causing conflicts.Here's an updated version of the
INSERTstatement forTTMTusing theYYYY-MM-DDformat:-- Insert Data into 'TTMT' INSERT INTO TTMT (mas, madg, ngaym, ngayht, ngayt) VALUES ('s01', 'mdg1', '2020-02-01', '2021-01-01', '2021-01-01'), ('s02', 'mdg2', '2010-07-02', '2021-01-01', '2022-01-01'), ('s03', 'mdg3', '1990-07-01', '2021-01-01', '2023-01-01'), ('s04', 'mdg4', '2012-02-02', '2021-01-01', '2022-01-01'), ('s05', 'mdg5', '2015-07-01', '2021-01-01', '2022-01-01'); SELECT * FROM TTMT;Key Changes:
- Ensure date strings are in the format
YYYY-MM-DD. This is a widely recognized format that SQL Server can interpret correctly regardless of server locale settings. - Check your SQL Server settings for any regional or cultural settings that might affect date parsing, in case your environment requires a different approach.
Additional Tips:
- If your server setting uses a different date format (like
DD/MM/YYYY), ensure that the date strings align with those settings, or set the server configuration to use theYYYY-MM-DDformat. - Always encapsulate date values within single quotes when inserting them into a
datetimecolumn.
- Ensure date strings are in the format