compare value year with with current year

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

compare value year with with current year

Author

Topic

tike
Starting Member

3 Posts

Posted - 2011-11-07 : 06:16:04

Hello all,

I just started learning SQL and ran into a problem.
I would like to create a check constraint to compare 2 values with each other. In this case a date(int) with the current date(year) and subtract the current date(year) with -15.

This is what i got so for:


create database dataBaseName

use dataBaseName
create table Car(
  year numeric(4) null
)

use dataBaseName
alter table Car
add constraint ch_yearcheck check(year < year(getdate()) - 15)

When i try to run this code i get the following Message: The ALTER TABLE statement conflicted with the CHECK constraint ch_yearcheck.

Hope someone can help me out here.

Cheers,

Tim

When The Going Gets Tough, The Tough Get Going!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 06:19:05

it works fine for me. didnt understand what issue you're facing. you're just missing a space in below statement


alter table Car
add constraint ch_year check(year < year(getdate()) - 15)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 06:26:05

ok. i think yu've some data in yourtable which is having year value that violates the condition given. try this first

select count(*) from Car wher [year] > year(getdate()) - 15

you need to remove the above data before you apply this check constraint

another way is to add with nocheck option to ignore applying the constraint for existing data


alter table Car with nocheck
add constraint ch_year check(year < year(getdate()) - 15)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tike
Starting Member

3 Posts

Posted - 2011-11-07 : 06:30:40

Thanks for your fast reply visakh16,

I just tried out the code myself and it works fine over here as well lol :).
I think i typed something wrong in my MS SQL Server Management Studio.

Thanks anyway!

cheers,

Tim

P.S. The code above is part of a bigger picture, but with your post i think i will find out whats causing the error.

When The Going Gets Tough, The Tough Get Going!

tike
Starting Member

3 Posts

Posted - 2011-11-07 : 06:37:45

Solved!

select count(*) from Car wher [year] > year(getdate()) - 15

outputted 10 values.

I made a mistake with the greater then ( > ) symbol it had to be a less then ( < ) symbol

Thanks visakh16 appreciate your help!

When The Going Gets Tough, The Tough Get Going!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 06:43:34

quote:
Originally posted by tike

Solved!

select count(*) from Car wher [year] > year(getdate()) - 15

outputted 10 values.

I made a mistake with the greater then ( > ) symbol it had to be a less then ( < ) symbol

Thanks visakh16 appreciate your help!

When The Going Gets Tough, The Tough Get Going!

welcome

glad that i could be of help

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-11-07 : 09:46:46

Add check constraint to table.

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 09:51:19

quote:
Originally posted by jassi.singh

Add check constraint to table.

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

after so many suggestions being provided, you got this finally?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources