pcs at azizaz.com
pcs at azizaz.com
Mon Oct 8 23:48:48 CDT 2007
Hi Arthur, Thanks for your comment. In the meantime I read and learned a bit more from the Help Section of SQL2005. a) If you use a character field to express a time element for example 9:43; 15:15; 3:15PM SQL will implicit cast it into a DateTime datatype with the date part of 1 Jan 1900 added to it when used in functions etc. b) If you use a DateTime field to store only the Hour:Min value that you are interested in, SQL will add current date into the field plus the Time component when adding it directly in the table using SSMS. Unless you take steps in your FE to ensure that all Hour:Min values entered has the same DateComponent entered you are in trouble if you start using the values in various calculations You can of course extract just the timepart, and cast the timepart as DateTime datatype again to ensure that all your Hour:Min values are on the same Day footing so to speak... But in your SQL table you will have a funny representation of your Hour:Min values - i.e. various Dates in the DatePart I came up with a solution that uses a Character Field for the Start and End Times for the Shifts. Value entered via Combobox with preset values like: 0:00 0:15 0:30 .... 23:30 23:45 23:59 If you are interested, this is my situation: I have a pool of casual employees (Relief Staff Candidates) who have indicated - within a 24 hour period - their - Default Shift Availability (StartTime - EndTime) - Exception Shift Availablity with StartDate and EndDate indicating the period that the Exception Shift Availability is in force... I have a number of Requested Relief Positions each one for a particular shift. For each one I need to match up a Relief Staff Candidate .... So for example a Requested Position can be from 10 October to 15 October 2008 Shift from 9:00 to 15:00. I need to filter on all casual employees that have said: I am by default available between X and Y except for the period between This Date and That Date where I am available between W and Z So first I have to figure out which Shift Availablity for each Casual record to use .... Then once I've determined that, I need to check if the Requested Shift falls within the Shift Availability and if so include the Casual Record in the Filter. With the help of two scalar valued functions that helps me determine whether to use the Default OR the Exception Shift availability (is there any 'overlap' between the Requested Period and the Exception Shift Period - then use Exeception Shift Period) and whether the Requested Shift falls within the Persons's shift availability I can now - using one Stored Procedure - filter out the relevant Relief Staff Candidates. The Shift is only one part of the overall filtering to match up a Relief Staff Candidate... Regards borge ---- Original message ---- >Date: Mon, 8 Oct 2007 09:22:09 -0400 >From: "Arthur Fuller" <fuller.artful at gmail.com> >Subject: Re: [dba-SQLServer] What datatype to use for a Time field >To: dba-sqlserver at databaseadvisors.com > >1. This is always a tough one conceptually, IME. I have wrestled with this >one a dozen times at least. I have finally concluded that a moment in time >implies a date and that either the database or the front end ought to supply >said date. Noon on Monday is not the same as Noon on Tuesday, except in >terms of presentation, and Noon on no date is a meaningless concept. > >This may seem an academic distinction to some readers, but I don't see any >viable alternatives. Your FE may just request an abstract time, such as >preferred lunch hour. In such a case I wouldn't use a datetime data type. In >fact, I just had one of these crop up, and fortunately the requirement was >"on the hour" -- so I created a lookup table called Hours and populated it >with two columns and 24 records, the first column being autonumber (1...24, >conveniently corresponding to military time), the second column being "1 PM" >etc. > >Either you want a real representation of date/time or you want an abstract >concept called Time. These two things are IMO quite different. Just my $.02 >on this subject. > >2. Drag the right edge of the column header out as far as you wish. > >hth, >Arthur > >On 10/8/07, pcs at azizaz.com <pcs at azizaz.com> wrote: >> >> Hi, >> >> What datatype to use when creating a field / column to >> handle handle time - say in 24 hour format - >> If I use DateTime datatype - when entering 10:00; 14:00 or >> 2:00 PM the system enters current date and the time - I just >> want the time part! >> >> In Access you could easily format the field to display the >> time element only. >> And if removing the Format element on the field - it still >> only displays the time element. >> >> What's 'best practice' is SQL? (using SQL2005) >> >> Another question: >> How to widen the last column in a view in SSMS ?? >> >> Regards >> Borge >> _______________________________________________ >> dba-SQLServer mailing list >> dba-SQLServer at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >> http://www.databaseadvisors.com >> >> >_______________________________________________ >dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >http://www.databaseadvisors.com >