Invalid Data type in sql-server-CodePudding

enter image description here

Msg 2715, Level 16, State 6, Line 51 Column, parameter, or variable #3: Cannot find data type Number.

Completion time: 2021-11-22T17:37:07.8833541 02:00

Ive recently started with SQL and I'm getting this error can anyone help me?

CREATE TABLE Orders(
    OrderID int NOT NULL,
    CustomerName varchar(255),
    OrderDate Number,
    ShipDate Number,

PRIMARY KEY (OrderID)
);

CodePudding user response：

Use date instead of number

CREATE TABLE Orders(
    OrderID int NOT NULL PRIMARY KEY,
    CustomerName varchar(255),
    OrderDate date,
    ShipDate date
)

CodePudding user response：

I agree with all those comments..

CREATE TABLE Orders(
    OrderID int NOT NULL,
    CustomerName varchar(255),
    OrderDate DATETIME2(0),
    ShipDate DATETIME2(0),

    CONSTRAINT PK_Orders_OrderId PRIMARY KEY (OrderID)
);

I don't imagine you'll need millisecond precision for an order/ship date but the time might be handy.. If you do need milliseconds, increase the number in parentheses after the datetime2. Don't be tempted to store datetimes in a non-datetime type; it's just all round a world of pain. Storing datetimes offers many opportunities to leverage proper date time handling, date math, date diffs, truncating to the nearest week/month/year etc.. And it prevents bad input and ambiguity. Your users would relish any opportunity to put an orderdate in as 20210431 (april 31st) or 101112 (12 nov 2010? 11 oct 2012?)

You might get to thinking "I don't need the time" - in which case you could consider using a DATE type of column, but if you can foresee that you'd use a time ("exactly when did the order come in? when was the order marked as shipped?"), stick one on. It's common to use datetime with the time part zeroed out to midnight if you only need date precision. Remember too that times on a date is like decimals on a number. 01-Apr-2020 00:00:00 is not the same as 01-Apr-2020 00:00:01 in the same way that 1 is not the same as 1.0001 - to this end, when searching datetimes, use a range. Want all the orders on 1 April? Do WHERE orderdate >= '2020-04-01' AND OrderDate < '2020-04-02' in preference to e.g. CAST(orderdate as DATE) = '2020-04-01'; do not call functions on table columns in a WHERE clause, if it can be avoided

I also specified a name for the PK constraint - SQLS comes up with some fairly ugly constraint names if you don't spec one.. For foreign key constraints I tend to name them FK_ParentTableNameSingular_ChildTableNamePlural e.g. FK_Customer_Orders but it's entirely up to you..

Final point; SQLS has a really good GUI, SSMS that I caqn see you have installed. You can make changes graphically and ask it to generate a change script, which can be a really handy way of learning the nuts and bolts. If you turn on "Auto generate change script" in the Table sub item of the Designers area of Options, you'll see a dialog like this when you save changes you make graphically to a table. A lot of it is fluff, and some of it is "the long way round" that might, for example, clone, drop, rename a table just to change one thing, when an ALTER TABLE would work, but all in it's a handy learning resource:

^{Image reproduced from https://dirkstrauss.com/easily-generate-table-change-scripts/}