SQL Server/T-SQL Tutorial/Data Types/smalldatetime

Материал из SQL эксперт
Перейти к: навигация, поиск

smalldatetime column with default value

   <source lang="sql">

3> CREATE TABLE StudentsArchive ( 4> AID int IDENTITY(1,1) PRIMARY KEY CLUSTERED, 5> type nvarchar(6) NOT NULL, 6> whenchanged smalldatetime NOT NULL DEFAULT Getdate(), 7> 8> StudentID int, 9> nFirstName nvarchar(30), 10> nLastName nvarchar(50), 11> oFirstName nvarchar(30), 12> oLastName nvarchar(50) 13> ) 14> GO 1> 2> drop table StudentsArchive; 3> GO 1></source>


smalldatetime type

   <source lang="sql">

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</source>