March 22, 2009 at 9:56 am
Hi
In my database I have the startDate, startTime, endDate adn endTime of a job. The user will need to enter the startDate and startTime days or weeks before the endDate and endTime. I want to ensure that the user cannot enter the startDate without also entering the startTime.
If I keep the startDate and startTime in a sperate table then I have no problem as I cant set both fields to NOT NULL, but then I will have extra joins to work with.
I would like to just have one table and keep all 4 fields in one table. So is it possible using a check constraint or other method to force the user to not forget to enter the startTime if the startDate has been entered and same for endTime if the endDate has been entered.
I'm new to working with constraints etc but want to find the best way of doing this before I start building my tables.
Thanks for you help
Asta 🙂
March 22, 2009 at 10:18 am
The first thing I'll suggest is that you merge the date and time columns. Storing them separately means a lot of extra work to do comparisons or calculations.
So, change the table so it has StartDateTime and EndDateTime in it. Then it's impossible for a date to be entered without a time. You can then add a single, very simple constraint to ensure that the EndDateTime, if it's entered, is greater than the StartDateTime
EndDateTime IS NULL OR EndDateTime > StartDateTime
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 22, 2009 at 10:25 am
Thank you,
I had thought of doing that, but then it is possible for them just to enter the date part of the field leaving the time part to default to 00:00:00, if a constraint can force them to enter the time in another filed then the user will be reminded to enter the correct value. I have 12 different chargehand using this database most of them don't like using it and tend to forget. So I need to introduce as many measures as possible to force/remind them to enter the information. Also I have a lot of calcuations using the date and time fields sometimes so at times its easier if I don't have to sperate the date and time.
Asta 🙂
March 22, 2009 at 11:00 am
keil.asta (3/22/2009)
I had thought of doing that, but then it is possible for them just to enter the date part of the field leaving the time part to default to 00:00:00,
Sure, but that can be fixed by it defaulting to getdate() or by validation on the client app. That kind of validation belongs in the front end, not in the DB. What happens if the time really is midnight?
Also I have a lot of calcuations using the date and time fields sometimes so at times its easier if I don't have to sperate the date and time.
Yup, which is why they should be one field if you need both.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 22, 2009 at 11:06 am
hmmm interesting, but getdate() will return now, the user will need to enter date and time which is not real time. they might enter the date and time the job has ended hours/days it actually occured has they may be busy doing other things, so getdate() wont work.
Asta 🙂
March 22, 2009 at 11:16 am
Then consider front-end validation. Honestly, that's where the 'you must enter a time' type validation belongs. By the time the data gets to SQL, there's no way to tell if that midnight time is intentional, or due to someone not entering a time.
If you insist, you can add the constraints
HOUR(StartDateTIme) ! = 0
HOUR(EndDateTIme) ! = 0 OR EndDateTIme IS NULL
but then what happens when you do need to add in a time somewhere between midnight and 1AM?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 22, 2009 at 11:25 am
I see, I I want the constraint or other method to not allow the user to close the database not at least without warning message. so the message should say, you cannot save the record because a startDate has been entered while the startTime field contains a NULL value.
Maybe I just need to stick with having the sartDate and startTime in a different table to endDate and endTime, that way I can set to NOT NULL.
If there is no other way then at least this works. My tutor said someone forgetting data entry is not enough of a reason.... but he does not have to work with my forgetfull user and the consequence of the time fields not being completed.
Thanks for you help
Asta
March 22, 2009 at 11:43 am
keil.asta (3/22/2009)
I see, I I want the constraint or other method to not allow the user to close the database not at least without warning message. so the message should say, you cannot save the record because a startDate has been entered while the startTime field contains a NULL value.
Constraints aren't warnings. They're errors that will roll back the entire change. If you want a warning, that should be a client-side warning.
Maybe I just need to stick with having the sartDate and startTime in a different table to endDate and endTime, that way I can set to NOT NULL.
You're setting yourself up for major pain in the future if you do it that way. Adding and splitting entire tables to force someone to enter a value is likely to cause far more problems than it fixes. You now have to worry about cross-table constraints (which require triggers) and you've got to force a 1-1 relationship, which can be very painful.
My tutor said someone forgetting data entry is not enough of a reason
He's right. Listen to him.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 22, 2009 at 11:52 am
You now what Gail I think you have me convinced. I can just add a reminder message like you said at the interface end. I've been silly. To used to just building in Access that my problem.
Thank you very much for arguing with me.
Perfect,
Thanks a million, I probably even quote you in my assignment.
Asta 😉
March 22, 2009 at 12:07 pm
keil.asta (3/22/2009)
You now what Gail I think you have me convinced. I can just add a reminder message like you said at the interface end.
I'm not suggesting a reminder. I'm suggesting a client-side error. So, in whatever piece of code submits the form, check to see if both the start date and start time are populated and, if not, raise an error and stop the form submission. The point is it's done in the client app before the call to the database starts.
It's easily possible in MS Access as well if you're using a bound form.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 22, 2009 at 1:48 pm
ah OK I see. thank again. Asta
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply