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:
how to use WebCat w. SSL & CyberCash (1998)
adding second sku (2000)
Error - Search / Showif (2000)
Help formatting search results w/ table (1997)
rotating thumbnails (1997)
creator code (1997)
Calculating multiple shipping... (1997)
WCS Newbie question (1997)
Smith Micro - no competition (2000)
[WebDNA] ErrorLog.txt generated by WebDNA (2010)
service stop and restart (1997)
Using Plug-In while running 1.6.1 (1997)
searchable list archive (1997)
Nested Loops and SHOWIFs (1997)
Malformed Pages (1999)
Template not found error messages (1997)
WebStar Secure on other machine (1997)
Sort Order on a page search (1997)
Include Not Including (2000)
SIMS setup (1998)