August 20, 2014 at 7:33 am
I need to add a check constraint on a field that checks the time of the date to make sure it is 23:59:59.997. When records are inserted the time should always be 23:59:59.997. Any suggestions?
Thanks in advance,
August 20, 2014 at 7:57 am
dirk.dromgoole (8/20/2014)
I need to add a check constraint on a field that checks the time of the date to make sure it is 23:59:59.997. When records are inserted the time should always be 23:59:59.997. Any suggestions?Thanks in advance,
Why do you need to enforce such a strange constraint? Is this because of doing date range queries? There are easier and better ways of doing those queries than forcing a constraint like this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 20, 2014 at 8:11 am
I suppose there are probably better ways to modify the query. Basically, the WHERE clause of the query is:
WHERE (GETDATE() - 1 <= EndDate)
If EndDate has a time of 00:00:00.000 or any time for that matter that is not the end of the day then that whole day is not calculated. EndDate with a time of 00:00:00.000 will be less than GETDATE()-1 so the whole day before will not be accounted for.
August 20, 2014 at 8:11 am
Sean Lange (8/20/2014)
dirk.dromgoole (8/20/2014)
I need to add a check constraint on a field that checks the time of the date to make sure it is 23:59:59.997. When records are inserted the time should always be 23:59:59.997. Any suggestions?Thanks in advance,
Why do you need to enforce such a strange constraint? Is this because of doing date range queries? There are easier and better ways of doing those queries than forcing a constraint like this.
+1 to this.
I also just want to verify, if data comes in with a time of 23:59:59.994 do you want:
1. the insert to fail
2. to change the data so the time is set to 23:59:59.997
Using a check constraint does 1. If you want 2 you either need to put the logic in the application, a stored procedure, or a trigger.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 20, 2014 at 8:22 am
I have thought about a table valued function or trigger or something like that which will automatically update the date to end of day. The inserts will be done manually so a failure on insert will not be such a big deal and whomever does the inserts will just have to look at the constraint. Although, it would be nice not to have to worry about that and that's why I'm looking for any suggestions on best possible solution.
August 20, 2014 at 8:22 am
dirk.dromgoole (8/20/2014)
I suppose there are probably better ways to modify the query. Basically, the WHERE clause of the query is:WHERE (GETDATE() - 1 <= EndDate)
If EndDate has a time of 00:00:00.000 or any time for that matter that is not the end of the day then that whole day is not calculated. EndDate with a time of 00:00:00.000 will be less than GETDATE()-1 so the whole day before will not be accounted for.
Much as I suspected. What you need to do is learn how to deal with these types of queries instead of changing or forcing inaccurate data. There are a number of possibilities here and I am not exactly sure which fits your needs exactly. However, our friend Lynn has a blog post with a bunch of very common date routines.
http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/[/url]
So for your example above you could do something like this.
WHERE dateadd(dd, datediff(dd, 0, GETDATE()) - 1, 0) <= EndDate
BTW, You should not get into the habit of things like getdate() - 1. You should be explicit and use dateadd.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 20, 2014 at 8:24 am
dirk.dromgoole (8/20/2014)
I have thought about a table valued function or trigger or something like that which will automatically update the date to end of day. The inserts will be done manually so a failure on insert will not be such a big deal and whomever does the inserts will just have to look at the constraint. Although, it would be nice not to have to worry about that and that's why I'm looking for any suggestions on best possible solution.
As data stewards our job is to NOT change data but to retrieve it accurately. Changing data so it can be queried is a sign that the query needs to be changed. Accurate data is critical to many things and it should be treated as sacred.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 20, 2014 at 8:51 am
I agree, thank you very much for your time on this matter. I am going to revise the query instead.
August 20, 2014 at 8:58 am
dirk.dromgoole (8/20/2014)
I agree, thank you very much for your time on this matter. I am going to revise the query instead.
Let me know if you run into any issues with your date logic. I will be happy to help you sort it out.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 20, 2014 at 9:21 am
dirk.dromgoole (8/20/2014)
I suppose there are probably better ways to modify the query. Basically, the WHERE clause of the query is:WHERE (GETDATE() - 1 <= EndDate)
If EndDate has a time of 00:00:00.000 or any time for that matter that is not the end of the day then that whole day is not calculated. EndDate with a time of 00:00:00.000 will be less than GETDATE()-1 so the whole day before will not be accounted for.
If you don't need time portion, then use Date datatype instead of DateTime. If you're stuck with existing columns of type DateTime and just need to restrict data entered, then you can implement a check constraint on the column. Comparing value entered to a re-cast of itself is an easy and reliable way to implement this specific type of check. For example:
create table #t
(
EndDate datetime
constraint ck_EndDate check (EndDate = cast(EndDate as date))
);
insert into #t ( EndDate ) values ('2014/08/20');
(1 row(s) affected)
insert into #t ( EndDate ) values ('2014/08/20 20:59:59.997');
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "ck_EndDate".
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 20, 2014 at 10:44 am
Eric M Russell (8/20/2014)
dirk.dromgoole (8/20/2014)
I suppose there are probably better ways to modify the query. Basically, the WHERE clause of the query is:WHERE (GETDATE() - 1 <= EndDate)
If EndDate has a time of 00:00:00.000 or any time for that matter that is not the end of the day then that whole day is not calculated. EndDate with a time of 00:00:00.000 will be less than GETDATE()-1 so the whole day before will not be accounted for.
If you don't need time portion, then use Date datatype instead of DateTime. If you're stuck with existing columns of type DateTime and just need to restrict data entered, then you can implement a check constraint on the column. Comparing value entered to a re-cast of itself is an easy and reliable way to implement this specific type of check. For example:
create table #t(
EndDate datetime
constraint ck_EndDate check (EndDate = cast(EndDate as date))
);
insert into #t ( EndDate ) values ('2014/08/20');(1 row(s) affected)
insert into #t ( EndDate ) values ('2014/08/20 20:59:59.997');Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "ck_EndDate".
As this was posted in a SQL Server 2005 forum the DATE data type is not available in this version of SQL Server.
August 20, 2014 at 11:24 am
Lynn Pettis (8/20/2014)
As this was posted in a SQL Server 2005 forum the DATE data type is not available in this version of SQL Server.
The following check constraint should work in a similar way under SQL Server 2005.
create table #t
(
EndDate datetime
constraint ck_EndDate check (EndDate = convert(char(8),EndDate,112))
);
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 20, 2014 at 5:44 pm
Sean Lange (8/20/2014)
BTW, You should not get into the habit of things like getdate() - 1. You should be explicit and use dateadd.
Heh... or just dump the newer date/time datatypes so that you can take these types of wonderful shortcuts. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply