DateTime Constraint Problem - 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

DateTime Constraint Problem

Author

Topic

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2009-11-04 : 07:41:16

Hello there

Im trying to create a constraint on a DateTime type. I have

DateCheck datetime null check (dateCheck >= January 1, 1900 and dateCheck <= june 6, 2079),

and i keep getting an error saying syntax error near '1'

just can not see whats wrong.

many thanks

Regards

Rob

MCTS certified

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-04 : 07:50:08

use ISO format
>= '19000101'
<= '20790606'

KH
[spoiler]Time is always against us[/spoiler]

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2009-11-04 : 07:58:13

excellent thank you

Rob

MCTS certified

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2009-11-04 : 08:36:39

Now when i insert to the column.

insert into table for datatime column
values ( '20090817')

i get

Arithmetic overflow error converting expression to data type datetime

MCTS certified

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-11-04 : 08:50:54

Have you tried ???

create table #sample (date_column datetime)
insert into #sample values('20090817')
select * from #sample

its working..

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2009-11-04 : 09:00:00

yes thats working,

but not with my table containing column

DateCheck datetime null check (dateCheck >= 19000101 and dateCheck <= 20790606),

MCTS certified

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-11-04 : 09:04:33

quote:
Originally posted by masterdineen

yes thats working,

but not with my table containing column

DateCheck datetime null check (dateCheck >= 19000101 and dateCheck <= 20790606),

MCTS certified

It will works for U

create table #sample (DateCheck datetime null check (dateCheck >= '19000101' and dateCheck <= '20790606') )

insert into #sample values('20090817')
select * from #sample

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2009-11-04 : 09:12:16

I have just created a temp table with the checkdate column

inserted a date, and still get the arithmetic overflow error

i can not see what im doing wrong

MCTS certified

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-11-04 : 09:13:39

Have you check my previous post???

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2009-11-04 : 09:19:00

yes. as mine says

i have created a temp table as you suggested. and im still getting and error with my column sytax

(DateCheck datetime null check (dateCheck >= '19000101' and dateCheck <= '20790606')

MCTS certified

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-11-04 : 09:23:54

Hi

can you post your table structure and insert query part.

-------------------------
R...

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2009-11-04 : 09:34:59

create table integTask (

PNumber int identity(1,1) primary key,

dateCheck datetime null check (datecheck >= 19000101 and dateCheck <= 20790606))

MCTS certified

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-11-04 : 09:46:24

quote:
Originally posted by masterdineen

create table integTask (

PNumber int identity(1,1) primary key,

dateCheck datetime null check (datecheck >= 19000101 and dateCheck <= 20790606))

MCTS certified

dateCheck datetime null check (datecheck >= '19000101' and dateCheck <= '20790606'))

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2009-11-04 : 09:50:33

arggg

something small again

thank you very much. sorry to have such an easy question

Thank you

MCTS certified

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-11-05 : 00:24:52

Welcome :)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources