Andy, The date field in SQL is to be deprecated and all dates should be held in DateTime format. That can sometimes produce a real problem where the SQL "Between" function is used as the default datetime for a date alone returns 00:00:00 as hours mins & seconds. so if you do a between using datetime values which have a defaulted hh:mm:ss as 0 then the end data DOESN'T include the whole of the end date. You need to force in 23:59:59 as the hh:mm:ss value. Either that or do a strictly "<" the end date+1. This really bit me a while ago in a big way where loads of sales were made on the last day of the month which wasn't included in my SQL extract....
Result: big red face for yours truly once I found the error! Dave -----Original Message----- From: ProFox [mailto:[email protected]] On Behalf Of AndyHC Sent: 22 August 2013 15:01 To: [email protected] Subject: Re: [NF] smalldatetime default value function Or, if you're not interested in hrs mins secs why not just date? On 22/08/2013 19:24, Stephen Russell wrote: > Sorry only first cup of coffee. Datetime > > > On Thu, Aug 22, 2013 at 8:35 AM, James Harvey <[email protected]> wrote: > >> Godaddy lists a datetime option, but no timedate? >> >> James E Harvey >> M.I.S. >> Hanover Shoe Farms, Inc. >> www.hanoverpa.com >> office: 717-637-8931 >> cell: 717-887-2565 >> fax: 717-637-6766 >> >> >> -----Original Message----- >> From: ProFox [mailto:[email protected]] On Behalf Of Stephen >> Russell >> Sent: Thursday, August 22, 2013 9:29 AM >> To: ProFox Email List >> Subject: Re: [NF] smalldatetime default value function >> >> Don't use smalltimedate as a heads up. convert the structure of the >> db today to regular timedate and your life will be much easier. >> >> >> On Thu, Aug 22, 2013 at 8:22 AM, James Harvey <[email protected]> >> wrote: >> >>> Trying to convert a default getdate() value in a MSSQL table into a >>> smalldatetime field. >>> >>> The goal is for the default value to be in the format "08/22/2013 >>> 00:00:000 AM" >>> >>> The table is in a web site hosted by Godaddy. >>> >>> They have an interface allowing us to edit the field's data type and >>> allows us to enter a default value. >>> >>> We are using a data type of smalldatetime and tried the >>> "(CONVERT([smalldatetime],getdate(),(0)))", but it is still >>> returning data that has minutes, seconds, etc. >>> >>> >>> >>> James E Harvey >>> M.I.S. >>> Hanover Shoe Farms, Inc. >>> www.hanoverpa.com >>> office: 717-637-8931 >>> cell: 717-887-2565 >>> fax: 717-637-6766 >>> >>> [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[email protected] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
