Need Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need Help

Author

Topic

sanlen
Starting Member

29 Posts

Posted - 2008-10-16 : 22:49:32

Hi,

I am the newbie to SQL Server.

I have two fields in my table of the SQL server which are start_date and end_date. I want to ensure that these start_date must be smaller than end_date when the data is entried.

Could you please advise? I tried with CHECK(end_date>start_date), and it does not work at all.

I also have the emp_id field, and want it to auto generated in the formate of E0001, E0002, E0003,....when data are entried

Could also please advise how can i get this done?

I am so sorry for any inconvenience. I look forward to hearing from you soon.

Best Reagards,
SANLEN

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2008-10-17 : 00:37:18

if these are in datetime format, I think you can do this

if (@end_date > @start_date){
// insert into table
}

I am assuming that @end_date and @start_date are input variables into your store procedure here.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-17 : 04:22:54

Why CHECK constraint didnt work for you? whats the datatype of start and end date?

sanlen
Starting Member

29 Posts

Posted - 2008-10-17 : 04:33:09

I tried with DATETIME data type.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-17 : 04:43:20

quote:
Originally posted by sanlen

I tried with DATETIME data type.

you got any error? or it didnt work?

sanlen
Starting Member

29 Posts

Posted - 2008-10-17 : 04:46:16

I got the following error:

Column CHECK constraint for column 'end_date' references another column, table 'Project'.
Msg 1750, Level 16, State 0, Line 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-17 : 04:51:14

quote:
Originally posted by sanlen

I got the following error:

Column CHECK constraint for column 'end_date' references another column, table 'Project'.
Msg 1750, Level 16, State 0, Line 1

can you show the full creation script?

sanlen
Starting Member

29 Posts

Posted - 2008-10-17 : 04:55:54

Yeah, as below:

CREATE TABLE Project
(
project_id VARCHAR(10) NOT NULL,
project_name VARCHAR(30) NOT NULL,
project_desc VARCHAR(50),
client_id INT,
emp_id INT,
start_date DATETIME NOT NULL,
end_date DATETIME CHECK(end_date>start_date),
billing_rate MONEY CHECK(billing_rate > 1000),

CONSTRAINT pk_project_id PRIMARY KEY (project_id),
CONSTRAINT fk_client_id FOREIGN KEY (client_id) REFERENCES Client(client_id),
CONSTRAINT fk_emp_id FOREIGN KEY (emp_id) REFERENCES Employee(emp_id)
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-17 : 05:03:33

[code]CREATE TABLE Project
(
project_id VARCHAR(10) NOT NULL,
project_name VARCHAR(30) NOT NULL,
project_desc VARCHAR(50),
client_id INT,
emp_id INT,
[start_date] DATETIME NOT NULL,
end_date DATETIME ,
billing_rate MONEY CHECK(billing_rate > 1000),
CONSTRAINT ck_datesequence CHECK(end_date>[start_date]),
CONSTRAINT pk_project_id PRIMARY KEY (project_id),
CONSTRAINT fk_client_id FOREIGN KEY (client_id) REFERENCES Client(client_id),
CONSTRAINT fk_emp_id FOREIGN KEY (emp_id) REFERENCES Employee(emp_id)
)[/code]

sanlen
Starting Member

29 Posts

Posted - 2008-10-17 : 05:13:40

It's working now. Thanks you very much, your guidiance is very useful for me. I am now trying to compare date from two different tables. Could you also please advise?

Thanks you very much for your time.

Best Regards,
SANLEN

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-17 : 05:17:31

quote:
Originally posted by sanlen

It's working now. Thanks you very much, your guidiance is very useful for me. I am now trying to compare date from two different tables. Could you also please advise?

Thanks you very much for your time.

Best Regards,
SANLEN

if both columns are datetime and they dont have time part (00:00:00) then use
t1.col1=t2.col2

if they have time part then strip it off and compare only datepart

DATEADD(dd,DATEDIFF(dd,0,t1.col1),0)=DATEADD(dd,DATEDIFF(dd,0,t2.col2),0)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources