SQL Server/T-SQL Tutorial/Date Functions/CURRENT TIMESTAMP

CHECK (entered_date >= CURRENT_TIMESTAMP)

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

datetime DEFAULT CURRENT_TIMESTAMP

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>

select CURRENT_TIMESTAMP

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)

SELECT @today = current_timestamp

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>