SQL Server/T-SQL Tutorial/Date Functions/CURRENT TIMESTAMP
Содержание
CHECK (entered_date >= CURRENT_TIMESTAMP)
<source lang="sql">
6> CREATE TABLE employee 7> ( 8> emp_id int NOT NULL PRIMARY KEY 9> CHECK (emp_id BETWEEN 0 AND 1000), 10> 11> emp_name varchar(30) NOT NULL CONSTRAINT no_nums 12> CHECK (emp_name NOT LIKE "%[0-9]%"), 13> 14> mgr_id int NOT NULL REFERENCES employee(emp_id), 15> 16> entered_date datetime NULL CHECK (entered_date >= 17> CURRENT_TIMESTAMP), 18> 19> entered_by int CHECK (entered_by IS NOT NULL), 20> CONSTRAINT valid_entered_by CHECK 21> (entered_by = SUSER_ID(NULL) AND 22> entered_by <> emp_id), 23> 24> CONSTRAINT valid_mgr CHECK (mgr_id <> emp_id OR emp_id=1), 25> 26> CONSTRAINT end_of_month CHECK (DATEPART(DAY, GETDATE()) < 28) 27> ) 28> GO 1> 2> EXEC sp_helpconstraint employee 3> GO Object Name
employee
constraint_type constraint_name
delete_action update_action status_enabled status_for_replication constraint_keys
-----------------------------------------------------
------------- ------------- -------------- ---------------------- ----------------------------------------------------------
CHECK on column emp_id CK__employee__emp_id__5C6D822E
(n/a) (n/a) Enabled Is_For_Replication ([emp_id]>=(0) AND [emp_id]<=(1000))
CHECK on column entered_date CK__employee__entere__5F49EED9
(n/a) (n/a) Enabled Is_For_Replication ([entered_date]>=getdate())
CHECK on column entered_by CK__employee__entere__603E1312
(n/a) (n/a) Enabled Is_For_Replication ([entered_by] IS NOT NULL)
CHECK Table Level end_of_month
(n/a) (n/a) Enabled Is_For_Replication (datepart(day,getdate())<(28))
FOREIGN KEY FK__employee__mgr_id__5E55CAA0
No Action No Action Enabled Is_For_Replication mgr_id
REFERENCES master.dbo.employee (emp_id)
CHECK on column emp_name no_nums
(n/a) (n/a) Enabled Is_For_Replication (NOT [emp_name] like "%[0-9]%")
PRIMARY KEY (clustered) PK__employee__5B795DF5
(n/a) (n/a) (n/a) (n/a) emp_id
CHECK Table Level valid_entered_by
(n/a) (n/a) Enabled Is_For_Replication ([entered_by]=suser_id(NULL) AND [entered_by]<>[emp_id])
CHECK Table Level valid_mgr
(n/a) (n/a) Enabled Is_For_Replication ([mgr_id]<>[emp_id] OR [emp_id]=(1))
Table is referenced by foreign key
master.dbo.employee: FK__employee__mgr_id__5E55CAA0
1> 2> 3> drop table employee; 4> GO</source>
datetime DEFAULT CURRENT_TIMESTAMP
<source lang="sql">
4> CREATE TABLE T ( 5> int1 int, 6> bit1 bit NOT NULL DEFAULT 0, 7> rvr1 timestamp, 8> usr1 nvarchar(128) DEFAULT USER, 9> createtime datetime DEFAULT CURRENT_TIMESTAMP 10> ) 11> GO 1> 2> INSERT T (int1, bit1) VALUES (3, 1) 3> GO (1 rows affected) 1> UPDATE T 2> set bit1 = 1 3> WHERE int1 = 2 4> GO (0 rows affected) 1> drop table t; 2> GO 1> 2></source>
select CURRENT_TIMESTAMP
<source lang="sql">
10> SELECT CURRENT_TIMESTAMP, USER, SYSTEM_USER, CURRENT_USER, 11> SESSION_USER 12> GO
-------------------------------------------------------------------------------------------------------------------------------- -----------------------------------------------
----------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
2008-08-17 13:20:57.503 dbo J\Administrator
dbo
dbo
(1 rows affected)</source>
SELECT @today = current_timestamp
<source lang="sql">
31> DECLARE @today datetime 32> SELECT @today = current_timestamp 33> 34> SELECT @today 35> SELECT CONVERT (varbinary(8), @today) 36> SELECT CONVERT (int, SUBSTRING (CONVERT (varbinary(8), @today), 1, 4)) 37> SELECT CONVERT (int, SUBSTRING (CONVERT (varbinary(8), @today), 5, 4)) 38> GO
2008-08-17 13:14:21.513 (1 rows affected)
0x00009AFB00DA2D56
(1 rows affected)
39675
(1 rows affected)
14298454
(1 rows affected) 1></source>
