в 13:46, 26 мая 2010

2010-05-26T13:46:16Z

Admin: 1 версия

2010-05-26T10:23:04Z

1 версия

Новая страница

== 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>

← Предыдущая	Версия 13:46, 26 мая 2010
(нет различий)

SQL Server/T-SQL Tutorial/Date Functions/CURRENT TIMESTAMP - История изменений

в 13:46, 26 мая 2010

Admin: 1 версия