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". - brian On 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:
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". - brian On 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!
Top Articles:
Talk List
The WebDNA community talk-list is the best place to get some help: several hundred extremely proficient programmers with an excellent knowledge of WebDNA and an excellent spirit will deliver all the tips and tricks you can imagine...
Related Readings:
PIXO (1997)
OT - how many HTML forms is too many? (2000)
Protect (1997)
Don't tick me off :) [elaspedtime] (1997)
Help with Shipping Costs (1997)
RequiredFields notes ... (1997)
multiple search commands (1997)
WebCat2 beta 11 - new prefs ... (1997)
Frames and WebCat (1997)
Appending current [date] to a database (1997)
Replacing a Word (1999)
Check boxes (1997)
[Sum] function? (1997)
Sorting by SKU Not Working (1998)
feature request-- [epoch] (2004)
Not really WebCat (1997)
[WebDNA] Sorry WebDNA server not running (2014)
[WebDNA] multi [sendmail] inside [search] (2012)
Limit on amount of data browsers can parse? (2003)
[WebDNA] Dynamic server feedback? (2009)