Professional Web Applications Themes

DATEDIFF Command Help - Macromedia Dreamweaver

Hi, can someone please help me solve the following problem. This datedifference command boggles me. I have a recorded date/time and I want to pull only those records whos date/time occured TODAY. And then within the past month, and then the past year... Can someone explain the coding to do those three things? Jordan...

  1. #1

    Default DATEDIFF Command Help

    Hi, can someone please help me solve the following problem. This
    datedifference command boggles me.

    I have a recorded date/time and I want to pull only those records whos
    date/time occured TODAY. And then within the past month, and then the past
    year... Can someone explain the coding to do those three things?

    Jordan


    Jordan Marton Guest

  2. #2

    Default Re: DATEDIFF Command Help

    Actually for what you are doing, it is more likely that you will want to use
    DateDiff 's cousin, DateAdd.

    Dim dtToday as Date
    Dim dtPastMonth as Date
    Dim dtPastYear as Date
    Dim sSqlToday as String
    Dim sSqlPastMonth as String
    Dim sSqlPastYear as String

    ' Get Date value for current day at "00:00:00"
    dtToday = Format$(now(),"mm/dd/yyyy")

    ' Jump back a month by passing a negative value to DateAdd
    dtPastMonth = DateAdd("m",-1,dtToday)

    ' Jump back a year by passing a negative value to DateAdd
    dtPastYear = DateAdd("yyyy",-1,dtToday)

    ' SQL To Extract Records For Today
    sSqlToday = "Select * From Table1 Where Date1 >= #" & dtToday & #"

    ' SQL To Extract Records For Past Year
    sSqlPastMonth = "Select * From Table1 Where Date1 >= #" & dtPastMonth & #"

    ' SQL To Extract Records For Past Year
    sSqlPastYear = "Select * From Table1 Where Date1 >= #" & dtPastYear & #"

    It gets a little trickier when you want records for the CURRENT MONTH or
    CURRENT YEAR as opposed to past month or past year, but that's still doable
    if you decide later that's what you want. Just give a holler.

    Hope that helps.

    Cheers,
    Sam
    ----- Original Message -----
    From: "Jordan Marton" <JMartonHAHAmarketaxess.com>
    Newsgroups: macromedia.dreamweaver
    Sent: Friday, July 11, 2003 4:09 AM
    Subject: DATEDIFF Command Help

    > Hi, can someone please help me solve the following problem. This
    > datedifference command boggles me.
    >
    > I have a recorded date/time and I want to pull only those records whos
    > date/time occured TODAY. And then within the past month, and then the past
    > year... Can someone explain the coding to do those three things?
    >
    > Jordan
    >
    >

    Sam Poikail Guest

  3. #3

    Default Re: DATEDIFF Command Help

    What is the DateAdd command?

    I'd prefer to have the current month/year. I will definitely use the today
    thing, and I guess will test out the others, I don't really understand the
    code tho.

    Jordan

    "Sam Poikail" <indigo_wolfhotmail.com> wrote in message
    news:belus2$foc$1forums.macromedia.com...
    > Actually for what you are doing, it is more likely that you will want to
    use
    > DateDiff 's cousin, DateAdd.
    >
    > Dim dtToday as Date
    > Dim dtPastMonth as Date
    > Dim dtPastYear as Date
    > Dim sSqlToday as String
    > Dim sSqlPastMonth as String
    > Dim sSqlPastYear as String
    >
    > ' Get Date value for current day at "00:00:00"
    > dtToday = Format$(now(),"mm/dd/yyyy")
    >
    > ' Jump back a month by passing a negative value to DateAdd
    > dtPastMonth = DateAdd("m",-1,dtToday)
    >
    > ' Jump back a year by passing a negative value to DateAdd
    > dtPastYear = DateAdd("yyyy",-1,dtToday)
    >
    > ' SQL To Extract Records For Today
    > sSqlToday = "Select * From Table1 Where Date1 >= #" & dtToday & #"
    >
    > ' SQL To Extract Records For Past Year
    > sSqlPastMonth = "Select * From Table1 Where Date1 >= #" & dtPastMonth &
    #"
    >
    > ' SQL To Extract Records For Past Year
    > sSqlPastYear = "Select * From Table1 Where Date1 >= #" & dtPastYear & #"
    >
    > It gets a little trickier when you want records for the CURRENT MONTH or
    > CURRENT YEAR as opposed to past month or past year, but that's still
    doable
    > if you decide later that's what you want. Just give a holler.
    >
    > Hope that helps.
    >
    > Cheers,
    > Sam
    > ----- Original Message -----
    > From: "Jordan Marton" <JMartonHAHAmarketaxess.com>
    > Newsgroups: macromedia.dreamweaver
    > Sent: Friday, July 11, 2003 4:09 AM
    > Subject: DATEDIFF Command Help
    >
    >
    > > Hi, can someone please help me solve the following problem. This
    > > datedifference command boggles me.
    > >
    > > I have a recorded date/time and I want to pull only those records whos
    > > date/time occured TODAY. And then within the past month, and then the
    past
    > > year... Can someone explain the coding to do those three things?
    > >
    > > Jordan
    > >
    > >
    >
    >

    Jordan Marton Guest

  4. #4

    Default Re: DATEDIFF Command Help

    The DateAdd command is kind of misnamed because it allows you to add OR
    substract (depending on whether a negative or positive value is used) time
    units to a date.

    Doentaion for it can be found at:
    [url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/vsfctdateadd.asp[/url]

    Sorry the code wasn't clear.... See if the additional comments make it any
    clearer.

    '
    ..---------------------------------------------------------------------------
    -----------------------------------
    ' | Declare 3 Date variables to store your comparison dates
    '
    '---------------------------------------------------------------------------
    -----------------------------------
    Dim dtToday as Date
    Dim dtPastMonth as Date
    Dim dtPastYear as Date

    '
    ..---------------------------------------------------------------------------
    -----------------------------------
    ' | Declare 3 String variables to store the SQL statements for the 3
    different
    ' | criteria.
    '
    '---------------------------------------------------------------------------
    -----------------------------------
    Dim sSqlToday as String
    Dim sSqlPastMonth as String
    Dim sSqlPastYear as String


    '
    ..---------------------------------------------------------------------------
    -----------------------------------
    ' | Get Date value for current day at "00:00:00"
    '
    ..---------------------------------------------------------------------------
    -----------------------------------
    dtToday = Format$(now(),"mm/dd/yyyy")

    '
    ..---------------------------------------------------------------------------
    -----------------------------------
    ' | Subtrace 1 month from the current date to obtain a date to use as
    criteria
    ' | for retrieving records from the past month.
    '
    ..---------------------------------------------------------------------------
    -----------------------------------
    dtPastMonth = DateAdd("m",-1,dtToday)

    '
    ..---------------------------------------------------------------------------
    -----------------------------------
    ' | Subtrace 1 year from the current date to obtain a date to use as
    criteria
    ' | for retrieving records from the past year.
    '
    ..---------------------------------------------------------------------------
    -----------------------------------
    dtPastYear = DateAdd("yyyy",-1,dtToday)

    '
    ..---------------------------------------------------------------------------
    -----------------------------------
    ' | Compose SQL for retrieving recorods for today. (Quote date criteria with
    ' | "#" characters. Table1 is the source table. Date1 is the identifying
    field
    ' | within the table that you will be comparing for dates.
    '
    ..---------------------------------------------------------------------------
    -----------------------------------
    sSqlToday = "Select * From Table1 Where Date1 >= #" & dtToday & "#"

    '
    ..---------------------------------------------------------------------------
    -----------------------------------
    ' | Compose SQL for retrieving recorods for the past month. (Quote date
    criteria
    ' | with "#" characters. Table1 is the source table. Date1 is the
    identifying field
    ' | within the table that you will be comparing for dates.
    '
    ..---------------------------------------------------------------------------
    -----------------------------------
    sSqlPastMonth = "Select * From Table1 Where Date1 >= #" & dtPastMonth & "#"

    '
    ..---------------------------------------------------------------------------
    -----------------------------------
    ' | Compose SQL for retrieving recorods for the past year. (Quote date
    criteria
    ' | with "#" characters. Table1 is the source table. Date1 is the
    identifying field
    ' | within the table that you will be comparing for dates.
    '
    ..---------------------------------------------------------------------------
    -----------------------------------
    sSqlPastYear = "Select * From Table1 Where Date1 >= #" & dtPastYear & "#"


    "Jordan Marton" <JMartonHAHAmarketaxess.com> wrote in message
    news:bem07u$i1l$1forums.macromedia.com...
    > What is the DateAdd command?
    >
    > I'd prefer to have the current month/year. I will definitely use the today
    > thing, and I guess will test out the others, I don't really understand the
    > code tho.
    >
    > Jordan
    >
    > "Sam Poikail" <indigo_wolfhotmail.com> wrote in message
    > news:belus2$foc$1forums.macromedia.com...
    > > Actually for what you are doing, it is more likely that you will want to
    > use
    > > DateDiff 's cousin, DateAdd.
    > >
    > > Dim dtToday as Date
    > > Dim dtPastMonth as Date
    > > Dim dtPastYear as Date
    > > Dim sSqlToday as String
    > > Dim sSqlPastMonth as String
    > > Dim sSqlPastYear as String
    > >
    > > ' Get Date value for current day at "00:00:00"
    > > dtToday = Format$(now(),"mm/dd/yyyy")
    > >
    > > ' Jump back a month by passing a negative value to DateAdd
    > > dtPastMonth = DateAdd("m",-1,dtToday)
    > >
    > > ' Jump back a year by passing a negative value to DateAdd
    > > dtPastYear = DateAdd("yyyy",-1,dtToday)
    > >
    > > ' SQL To Extract Records For Today
    > > sSqlToday = "Select * From Table1 Where Date1 >= #" & dtToday & #"
    > >
    > > ' SQL To Extract Records For Past Year
    > > sSqlPastMonth = "Select * From Table1 Where Date1 >= #" & dtPastMonth &
    > #"
    > >
    > > ' SQL To Extract Records For Past Year
    > > sSqlPastYear = "Select * From Table1 Where Date1 >= #" & dtPastYear &
    #"
    > >
    > > It gets a little trickier when you want records for the CURRENT MONTH or
    > > CURRENT YEAR as opposed to past month or past year, but that's still
    > doable
    > > if you decide later that's what you want. Just give a holler.
    > >
    > > Hope that helps.
    > >
    > > Cheers,
    > > Sam
    > > ----- Original Message -----
    > > From: "Jordan Marton" <JMartonHAHAmarketaxess.com>
    > > Newsgroups: macromedia.dreamweaver
    > > Sent: Friday, July 11, 2003 4:09 AM
    > > Subject: DATEDIFF Command Help
    > >
    > >
    > > > Hi, can someone please help me solve the following problem. This
    > > > datedifference command boggles me.
    > > >
    > > > I have a recorded date/time and I want to pull only those records whos
    > > > date/time occured TODAY. And then within the past month, and then the
    > past
    > > > year... Can someone explain the coding to do those three things?
    > > >
    > > > Jordan
    > > >
    > > >
    > >
    > >
    >
    >

    Sam Poikail Guest

  5. #5

    Default Re: DATEDIFF Command Help

    That's brilliant. Thanks. The comments helped, and so does knowing what the
    dateadd function is... ;-)

    Jordan

    "Sam Poikail" <indigo_wolfhotmail.com> wrote in message
    news:bem6nj$si4$1forums.macromedia.com...
    > The DateAdd command is kind of misnamed because it allows you to add OR
    > substract (depending on whether a negative or positive value is used) time
    > units to a date.
    >
    > Doentaion for it can be found at:
    >
    [url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/ht[/url]
    ml/vsfctdateadd.asp
    >
    > Sorry the code wasn't clear.... See if the additional comments make it
    any
    > clearer.
    >
    > '
    >
    ..---------------------------------------------------------------------------
    > -----------------------------------
    > ' | Declare 3 Date variables to store your comparison dates
    > '
    >
    '---------------------------------------------------------------------------
    > -----------------------------------
    > Dim dtToday as Date
    > Dim dtPastMonth as Date
    > Dim dtPastYear as Date
    >
    > '
    >
    ..---------------------------------------------------------------------------
    > -----------------------------------
    > ' | Declare 3 String variables to store the SQL statements for the 3
    > different
    > ' | criteria.
    > '
    >
    '---------------------------------------------------------------------------
    > -----------------------------------
    > Dim sSqlToday as String
    > Dim sSqlPastMonth as String
    > Dim sSqlPastYear as String
    >
    >
    > '
    >
    ..---------------------------------------------------------------------------
    > -----------------------------------
    > ' | Get Date value for current day at "00:00:00"
    > '
    >
    ..---------------------------------------------------------------------------
    > -----------------------------------
    > dtToday = Format$(now(),"mm/dd/yyyy")
    >
    > '
    >
    ..---------------------------------------------------------------------------
    > -----------------------------------
    > ' | Subtrace 1 month from the current date to obtain a date to use as
    > criteria
    > ' | for retrieving records from the past month.
    > '
    >
    ..---------------------------------------------------------------------------
    > -----------------------------------
    > dtPastMonth = DateAdd("m",-1,dtToday)
    >
    > '
    >
    ..---------------------------------------------------------------------------
    > -----------------------------------
    > ' | Subtrace 1 year from the current date to obtain a date to use as
    > criteria
    > ' | for retrieving records from the past year.
    > '
    >
    ..---------------------------------------------------------------------------
    > -----------------------------------
    > dtPastYear = DateAdd("yyyy",-1,dtToday)
    >
    > '
    >
    ..---------------------------------------------------------------------------
    > -----------------------------------
    > ' | Compose SQL for retrieving recorods for today. (Quote date criteria
    with
    > ' | "#" characters. Table1 is the source table. Date1 is the identifying
    > field
    > ' | within the table that you will be comparing for dates.
    > '
    >
    ..---------------------------------------------------------------------------
    > -----------------------------------
    > sSqlToday = "Select * From Table1 Where Date1 >= #" & dtToday & "#"
    >
    > '
    >
    ..---------------------------------------------------------------------------
    > -----------------------------------
    > ' | Compose SQL for retrieving recorods for the past month. (Quote date
    > criteria
    > ' | with "#" characters. Table1 is the source table. Date1 is the
    > identifying field
    > ' | within the table that you will be comparing for dates.
    > '
    >
    ..---------------------------------------------------------------------------
    > -----------------------------------
    > sSqlPastMonth = "Select * From Table1 Where Date1 >= #" & dtPastMonth &
    "#"
    >
    > '
    >
    ..---------------------------------------------------------------------------
    > -----------------------------------
    > ' | Compose SQL for retrieving recorods for the past year. (Quote date
    > criteria
    > ' | with "#" characters. Table1 is the source table. Date1 is the
    > identifying field
    > ' | within the table that you will be comparing for dates.
    > '
    >
    ..---------------------------------------------------------------------------
    > -----------------------------------
    > sSqlPastYear = "Select * From Table1 Where Date1 >= #" & dtPastYear & "#"
    >
    >
    > "Jordan Marton" <JMartonHAHAmarketaxess.com> wrote in message
    > news:bem07u$i1l$1forums.macromedia.com...
    > > What is the DateAdd command?
    > >
    > > I'd prefer to have the current month/year. I will definitely use the
    today
    > > thing, and I guess will test out the others, I don't really understand
    the
    > > code tho.
    > >
    > > Jordan
    > >
    > > "Sam Poikail" <indigo_wolfhotmail.com> wrote in message
    > > news:belus2$foc$1forums.macromedia.com...
    > > > Actually for what you are doing, it is more likely that you will want
    to
    > > use
    > > > DateDiff 's cousin, DateAdd.
    > > >
    > > > Dim dtToday as Date
    > > > Dim dtPastMonth as Date
    > > > Dim dtPastYear as Date
    > > > Dim sSqlToday as String
    > > > Dim sSqlPastMonth as String
    > > > Dim sSqlPastYear as String
    > > >
    > > > ' Get Date value for current day at "00:00:00"
    > > > dtToday = Format$(now(),"mm/dd/yyyy")
    > > >
    > > > ' Jump back a month by passing a negative value to DateAdd
    > > > dtPastMonth = DateAdd("m",-1,dtToday)
    > > >
    > > > ' Jump back a year by passing a negative value to DateAdd
    > > > dtPastYear = DateAdd("yyyy",-1,dtToday)
    > > >
    > > > ' SQL To Extract Records For Today
    > > > sSqlToday = "Select * From Table1 Where Date1 >= #" & dtToday & #"
    > > >
    > > > ' SQL To Extract Records For Past Year
    > > > sSqlPastMonth = "Select * From Table1 Where Date1 >= #" & dtPastMonth
    &
    > > #"
    > > >
    > > > ' SQL To Extract Records For Past Year
    > > > sSqlPastYear = "Select * From Table1 Where Date1 >= #" & dtPastYear &
    > #"
    > > >
    > > > It gets a little trickier when you want records for the CURRENT MONTH
    or
    > > > CURRENT YEAR as opposed to past month or past year, but that's still
    > > doable
    > > > if you decide later that's what you want. Just give a holler.
    > > >
    > > > Hope that helps.
    > > >
    > > > Cheers,
    > > > Sam
    > > > ----- Original Message -----
    > > > From: "Jordan Marton" <JMartonHAHAmarketaxess.com>
    > > > Newsgroups: macromedia.dreamweaver
    > > > Sent: Friday, July 11, 2003 4:09 AM
    > > > Subject: DATEDIFF Command Help
    > > >
    > > >
    > > > > Hi, can someone please help me solve the following problem. This
    > > > > datedifference command boggles me.
    > > > >
    > > > > I have a recorded date/time and I want to pull only those records
    whos
    > > > > date/time occured TODAY. And then within the past month, and then
    the
    > > past
    > > > > year... Can someone explain the coding to do those three things?
    > > > >
    > > > > Jordan
    > > > >
    > > > >
    > > >
    > > >
    > >
    > >
    >
    >

    Jordan Marton Guest

Similar Threads

  1. Datediff() bug
    By OldCFer in forum Macromedia ColdFusion
    Replies: 21
    Last Post: November 24th, 09:35 AM
  2. dateDiff Question
    By Winston2 in forum Coldfusion - Getting Started
    Replies: 2
    Last Post: November 2nd, 02:30 PM
  3. now() datediff inconsistency
    By theriversideweb in forum Macromedia ColdFusion
    Replies: 11
    Last Post: May 16th, 06:05 PM
  4. Datediff
    By Alexander Hoffmann in forum PHP Development
    Replies: 1
    Last Post: December 11th, 08:34 PM
  5. DateDiff in C#
    By Mark Fox in forum ASP.NET General
    Replies: 3
    Last Post: July 16th, 01:26 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139