smalldatetime type : smalldatetime « Data Types « SQL Server / T-SQL Tutorial





6> CREATE TABLE Orders (
7>      OrderID int IDENTITY (1, 1) NOT NULL ,
8>      CustomerID nchar (5) NULL ,
9>      EmployeeID int NULL ,
10>     OrderDate datetime NULL ,
11>     RequiredDate datetime NULL ,
12>     ShippedDate datetime NULL ,
13>     ShipVia int NULL ,
14>     Freight money NULL DEFAULT (0),
15>     ShipName nvarchar (40) NULL ,
16>     ShipAddress nvarchar (60) NULL ,
17>     ShipCity nvarchar (15) NULL ,
18>     ShipRegion nvarchar (15) NULL ,
19>     ShipPostalCode nvarchar (10) NULL ,
20>     ShipCountry nvarchar (15) NULL)
21> GO
1>
2>    Create PROC spInsertDateValidatedOrder
3>       @CustomerID     nvarchar(5),
4>       @EmployeeID     int,
5>       @OrderDate      datetime     = NULL,
6>       @RequiredDate   datetime     = NULL,
7>       @ShippedDate    datetime     = NULL,
8>       @ShipVia        int,
9>       @Freight        money,
10>       @ShipName       nvarchar(40) = NULL,
11>       @ShipAddress    nvarchar(60) = NULL,
12>       @ShipCity       nvarchar(15) = NULL,
13>       @ShipRegion     nvarchar(15) = NULL,
14>       @ShipPostalCode nvarchar(10) = NULL,
15>       @ShipCountry    nvarchar(15) = NULL,
16>       @OrderID        int      OUTPUT
17>
18>    AS
19>    DECLARE   @InsertedOrderDate    smalldatetime
20>    IF DATEDIFF(dd, @OrderDate, GETDATE()) > 7
21>       SELECT @InsertedOrderDate = NULL
22>    ELSE
23>       SELECT @InsertedOrderDate = CONVERT(datetime,(CONVERT(varchar,@OrderDate,112)))
24>    INSERT INTO Orders VALUES (@CustomerID,@EmployeeID,@InsertedOrderDate,@RequiredDate,@ShippedDate,      @ShipVia,@Freight,@ShipName,@ShipAddress,@ShipCity,@ShipRegion,@ShipPostalCode,@ShipCountry
25>    )
26>    SELECT @OrderID = @@IDENTITY
27>    GO
1>
2>
3> drop table Orders;
4> GO
1>
2> drop PROC spInsertDateValidatedOrder;
3> GO










5.20.smalldatetime
5.20.1.smalldatetime type
5.20.2.smalldatetime column with default value