Exactly, so it looks like taking care of the "between" issue in the select
will work, rather than trying to get the syntax of a function for getdate()
to return 00:00:00 as the time.
This seems to be working now.
mcfdate = DTOC(Thisform.stpageframe1.Page2.stcntdownload.sttxtfdate.Value) +
" 00:00:00"
mcldate = DTOC(Thisform.stpageframe1.Page2.stcntdownload.sttxtldate.Value) +
" 23:59:59"
m.lcxn = ("Driver={SQL Server}; Server=server name;
Database=database name; Uid=uid name; Pwd=password")
m.lih = Sqlstringconnect(m.lcxn)
lc_select1 = "select
usta,firstname,lastname,address1,address2,city,state,zipcode,country,email,p
hone,truckingcompany,insurancecompany,agentatsale,agentphone,subdate "
lc_select8 = "FROM hsf_horsesale where subdate between
?mcfdate and ?mcldate"
lc_selectcommand = lc_select1 + lc_select8
SQLExec(m.lih,lc_selectcommand)
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 Dave Crozier
Sent: Thursday, August 22, 2013 10:25 AM
To: ProFox Email List
Subject: RE: [NF] smalldatetime default value function
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.
