Re: Dates and Math - Simple Subtraction

This WebDNA talk-list message is from

2004

It keeps the original formatting. numero = 58891
interpreted = N
texte = WebDNA, like most any computer program, will convert dates to numbers before comparing. In WebDNA's case, it converts the date to the number of days since 01/01/0000 (that having a math date value of "1").You can see this for yourself by playing with a little code: [math date]1[/math] returns "01/01/0000", [math date]2[/math] returns "01/02/0000", etc.Both [math]{07/04/04}[/math] and [math]{07/04/2004}[/math] (2- and 4-digit years) return the same value: "732131"(note: your results may vary depending on how your WebDNA date and century break preferences are set up)So, since both 07/04/04 and 07/04/2004 translate to the number 732131, they are considered equal. Any date that translates to a number greater than 732131 would be considered after 07/04/04.One thing that I note looking deeper into your original post is that you said your date calculations (in step 2) resulted in the value "34870". Doing a [math date]34870[/math] gives the date "06/21/0095", and I doubt you sold any vehicles before that date. This may have been due to a calculation problem, but could also be a WebDNA preferences issue of some sort.So... the date returned by my example, with its 4-digit year, should work fine in your search since (internally) WebDNA converts all dates to numbers before comparing them.I didn't mean to cause confusion, but while CONCEPTUALLY the type=date parameter says "treat this field as a date", TECHNICALLY it says "assuming the field is properly formatted as a human-readable date that WebDNA understands, convert it to a number for comparisons and sorting".- brianOn Jul 26, 2004, at 4:24 PM, David Cate wrote:> Brian,>> Thanks. I must be misunderstanding the type=date conversion then. I > guess I assumed that this instruction would identify "SOLD' as a date > instead of a number? According to your notes, I was looking at it > upside down. You say the type=date...>> convert your parameter from a date to a number AND the SOLD value >> from every record in your database from a date to a number?>>> The short snippet here>>>> [text]SOLDDATE=[math date]{[date]}-14[/math][/text]> generates a 00/00/0000 value. The values that in our database are > spitting out a 00/00/00 number.>> The numbers from our database come out with a fixed value and I'm > trying to compare a set of vehicles that are sold within the last 14 > days. I do not want to return vehicles that were sold farther back > than 14.>> David>> On Jul 26, 2004, at 6:58 PM, Brian Fries wrote:>>> You cannot specify a format with the [math date] syntax.>>>> However, since your search specifies "SOLDtype=date", what WebDNA >> does internally is convert your parameter from a date to a number AND >> the SOLD value from every record in your database from a date to a >> number, then compares them as numbers. So, WebDNA will be comparing >> numbers to numbers as long as it understands the human-readable dates >> provided, which it does in your case.>>>> Note that this is one reason many WebDNA developers choose to store >> dates in their databases as [math]{[date]}[/math] values, rather than >> as human-readable dates. Searches are faster because WebDNA doesn't >> need to convert the dates to numbers.>>>> - brian>>>> On Jul 26, 2004, at 3:30 PM, David Cate wrote:>>>>> Oh yes! I saw that before I sent it, but still didn't work.>>>>>> I do like the simple step, but what if I'm searching for a six >>> character date? 00/00/00>>>>>> Can I use the format with the [math date]>>> i.e. [math date format=%m/%d/%y]>>>>>> Thanks for taking time out with a hack to help...>>>>>> David>>>>>> On Jul 26, 2004, at 5:58 PM, Brian Fries wrote:>>>>>>> I step 3), you use a variable named [THREEWEEK] - your previous >>>> steps created the [TWOWEEK] variable.>>>>>>>> You should be able to simplify this into one easy step:>>>>>>>> [text]SOLDDATE=[math date]{[date]}-14[/math][/text]>>>>>>>> The {[date]} results in a number (days since the year 0000), from >>>> which you can simply subtract 14 days.>>>>>>>> The [math date] turns that number back into a human-formatted date >>>> (using you preferences settings).>>>>>>>> - brian>>>>>>>> On Jul 26, 2004, at 2:12 PM, David Cate wrote:>>>>>>>>> Hello,>>>>>>>>>> I'm trying to only show records that exist within the past two >>>>> weeks. If an item was sold more than two weeks ago, we don't want >>>>> to display the record.>>>>>>>>>> Can someone tell me where I've gone wrong?>>>>>>>>>> 1. Define [TODAY[>>>>> [text]TODAY=[date %m/%d/%y][/text]>>>>> (Note: Database already carries 00/00/00 value) - Number turns out >>>>> fine>>>>>>>>>> 2. Define two week value>>>>> [text]TWOWEEK=[Math]{[TODAY]}-{00/14/00}[/Math][/text]>>>>> (This seems to be the problem - This turns out a number 34870 - >>>>> not sure how that happened.>>>>>>>>>> 3. Format TWO WEEK to var [SOLDDATE]>>>>> [text]SOLDDATE=[format days_to_date >>>>> %m/%d/%y][THREEWEEK][/format][/text]>>>>> (value turns out 00/00/00)>>>>>>>>>> Here's the search query...which obviously does not work. Anyone >>>>> have any suggestions for anything for me look at?>>>>> [search >>>>> db=admin/cars- >>>>> n.db&geSTOCKdata=[blank]&geSolddata=[SOLDDATE]&SOLDtype=date]>>>>>>>>>> Thanks in advance.>>>>>>>>> -- Brian Fries, BrainScan Software -- >>>> http://www.brainscansoftware.com -->>>>>> ------------------------------------------------------------->> This message is sent to you because you are subscribed to>> the mailing list .>> To unsubscribe, E-mail to: >> To switch to the DIGEST mode, E-mail to >> >> Web Archive of this list is at: http://webdna.smithmicro.com/>>>>> -------------------------------------------------------------> This message is sent to you because you are subscribed to> the mailing list .> To unsubscribe, E-mail to: > To switch to the DIGEST mode, E-mail to > > Web Archive of this list is at: http://webdna.smithmicro.com/>-------------------------------------------------------------This message is sent to you because you are subscribed to the mailing list .To unsubscribe, E-mail to: To switch to the DIGEST mode, E-mail to Web Archive of this list is at: http://webdna.smithmicro.com/ Associated Messages, from the most recent to the oldest:

Re: Dates and Math - Simple Subtraction ( Brian Fries 2004)
Re: Dates and Math - Simple Subtraction ( David Cate 2004)
Re: Dates and Math - Simple Subtraction ( "Gary Krockover" 2004)
Re: Dates and Math - Simple Subtraction ( Brian Fries 2004)
Re: Dates and Math - Simple Subtraction ( David Cate 2004)
Re: Dates and Math - Simple Subtraction ( Brian Fries 2004)
Dates and Math - Simple Subtraction ( David Cate 2004)

WebDNA, like most any computer program, will convert dates to numbers before comparing. In WebDNA's case, it converts the date to the number of days since 01/01/0000 (that having a math date value of "1").You can see this for yourself by playing with a little code: [math date]1[/math] returns "01/01/0000", [math date]2[/math] returns "01/02/0000", etc.Both [math]{07/04/04}[/math] and [math]{07/04/2004}[/math] (2- and 4-digit years) return the same value: "732131"(note: your results may vary depending on how your WebDNA date and century break preferences are set up)So, since both 07/04/04 and 07/04/2004 translate to the number 732131, they are considered equal. Any date that translates to a number greater than 732131 would be considered after 07/04/04.One thing that I note looking deeper into your original post is that you said your date calculations (in step 2) resulted in the value "34870". Doing a [math date]34870[/math] gives the date "06/21/0095", and I doubt you sold any vehicles before that date. This may have been due to a calculation problem, but could also be a WebDNA preferences issue of some sort.So... the date returned by my example, with its 4-digit year, should work fine in your search since (internally) WebDNA converts all dates to numbers before comparing them.I didn't mean to cause confusion, but while CONCEPTUALLY the type=date parameter says "treat this field as a date", TECHNICALLY it says "assuming the field is properly formatted as a human-readable date that WebDNA understands, convert it to a number for comparisons and sorting".- brianOn Jul 26, 2004, at 4:24 PM, David Cate wrote:> Brian,>> Thanks. I must be misunderstanding the type=date conversion then. I > guess I assumed that this instruction would identify "SOLD' as a date > instead of a number? According to your notes, I was looking at it > upside down. You say the type=date...>> convert your parameter from a date to a number AND the SOLD value >> from every record in your database from a date to a number?>>> The short snippet here>>>> [text]SOLDDATE=[math date]{[date]}-14[/math][/text]> generates a 00/00/0000 value. The values that in our database are > spitting out a 00/00/00 number.>> The numbers from our database come out with a fixed value and I'm > trying to compare a set of vehicles that are sold within the last 14 > days. I do not want to return vehicles that were sold farther back > than 14.>> David>> On Jul 26, 2004, at 6:58 PM, Brian Fries wrote:>>> You cannot specify a format with the [math date] syntax.>>>> However, since your search specifies "SOLDtype=date", what WebDNA >> does internally is convert your parameter from a date to a number AND >> the SOLD value from every record in your database from a date to a >> number, then compares them as numbers. So, WebDNA will be comparing >> numbers to numbers as long as it understands the human-readable dates >> provided, which it does in your case.>>>> Note that this is one reason many WebDNA developers choose to store >> dates in their databases as [math]{[date]}[/math] values, rather than >> as human-readable dates. Searches are faster because WebDNA doesn't >> need to convert the dates to numbers.>>>> - brian>>>> On Jul 26, 2004, at 3:30 PM, David Cate wrote:>>>>> Oh yes! I saw that before I sent it, but still didn't work.>>>>>> I do like the simple step, but what if I'm searching for a six >>> character date? 00/00/00>>>>>> Can I use the format with the [math date]>>> i.e. [math date format=%m/%d/%y]>>>>>> Thanks for taking time out with a hack to help...>>>>>> David>>>>>> On Jul 26, 2004, at 5:58 PM, Brian Fries wrote:>>>>>>> I step 3), you use a variable named [THREEWEEK] - your previous >>>> steps created the [TWOWEEK] variable.>>>>>>>> You should be able to simplify this into one easy step:>>>>>>>> [text]SOLDDATE=[math date]{[date]}-14[/math][/text]>>>>>>>> The {[date]} results in a number (days since the year 0000), from >>>> which you can simply subtract 14 days.>>>>>>>> The [math date] turns that number back into a human-formatted date >>>> (using you preferences settings).>>>>>>>> - brian>>>>>>>> On Jul 26, 2004, at 2:12 PM, David Cate wrote:>>>>>>>>> Hello,>>>>>>>>>> I'm trying to only show records that exist within the past two >>>>> weeks. If an item was sold more than two weeks ago, we don't want >>>>> to display the record.>>>>>>>>>> Can someone tell me where I've gone wrong?>>>>>>>>>> 1. Define [TODAY[>>>>> [text]TODAY=[date %m/%d/%y][/text]>>>>> (Note: Database already carries 00/00/00 value) - Number turns out >>>>> fine>>>>>>>>>> 2. Define two week value>>>>> [text]TWOWEEK=[math]{[TODAY]}-{00/14/00}[/Math][/text]>>>>> (This seems to be the problem - This turns out a number 34870 - >>>>> not sure how that happened.>>>>>>>>>> 3. Format TWO WEEK to var [SOLDDATE]>>>>> [text]SOLDDATE=[format days_to_date >>>>> %m/%d/%y][THREEWEEK][/format][/text]>>>>> (value turns out 00/00/00)>>>>>>>>>> Here's the search query...which obviously does not work. Anyone >>>>> have any suggestions for anything for me look at?>>>>> [search >>>>> db=admin/cars- >>>>> n.db&geSTOCKdata=[blank]&geSolddata=[SOLDDATE]&SOLDtype=date]>>>>>>>>>> Thanks in advance.>>>>>>>>> -- Brian Fries, BrainScan Software -- >>>> http://www.brainscansoftware.com -->>>>>> ------------------------------------------------------------->> This message is sent to you because you are subscribed to>> the mailing list .>> To unsubscribe, E-mail to: >> To switch to the DIGEST mode, E-mail to >> >> Web Archive of this list is at: http://webdna.smithmicro.com/>>>>> -------------------------------------------------------------> This message is sent to you because you are subscribed to> the mailing list .> To unsubscribe, E-mail to: > To switch to the DIGEST mode, E-mail to > > Web Archive of this list is at: http://webdna.smithmicro.com/>-------------------------------------------------------------This message is sent to you because you are subscribed to the mailing list .To unsubscribe, E-mail to: To switch to the DIGEST mode, E-mail to Web Archive of this list is at: http://webdna.smithmicro.com/ Brian Fries

DOWNLOAD WEBDNA NOW!

Re: Dates and Math - Simple Subtraction

2004

Top Articles:

Related Readings: