Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2011
    Posts
    2

    Unanswered: date interval help

    Hi, I'm working on converting some MSSQL queries to MySQL for the TPC-H benchmark. I have everything working fine, except that I don't understand the meaning of this statement in MSSQL:
    Code:
    where
            l_shipdate <= date '1998-12-01' - interval ':1' day (3)
    The thing that I specifically don't understand is ":1" and the "(3)" part of the interval command. I converted some other MSSQL queries of this type to MySQL but they were more standard and didn't have these symbols. An example of that conversion that I did is below.

    The conversion I did for the last query is from this (MSSQL)
    Code:
    where o_orderdate < date '1997-01-01' + interval '1' year
    to this (MySQL)
    Code:
    where o_orderdate < DATE_ADD('1997-01-01', INTERVAL 1 YEAR)
    but you can see there are no colons or numbers in parenthesis at the end like the query at the top, so the conversion was relatively straightforward. How do I convert ":1" and "(3)" to fit in the syntax of MySQL? I'd really just like to know the meaning of ":1" and "(3)" in the interval command at the top.

    Thanks in advance!!

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Are you certain that is valid MSSQL Server syntax?

  3. #3
    Join Date
    Mar 2011
    Posts
    2

    Not Exactly

    Well no, I'm not exactly sure. I didn't write it myself though, it was generated from a template by the TPC-H qgen program. I reran the query generator and it seems like the :1 part of the query was a bug or something, because all other queries generated don't have a colon there.

    I still have the problem of what the (3) means at the end though. This is from a newly generated query:
    Code:
    where
    	l_shipdate <= date '1998-12-01' - interval '71' day (3)
    This is the entire query, for context.

    Code:
    select
    	l_returnflag,
    	l_linestatus,
    	sum(l_quantity) as sum_qty,
    	sum(l_extendedprice) as sum_base_price,
    	sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
    	sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
    	avg(l_quantity) as avg_qty,
    	avg(l_extendedprice) as avg_price,
    	avg(l_discount) as avg_disc,
    	count(*) as count_order
    from
    	lineitem
    where
    	l_shipdate <= date '1998-12-01' - interval '71' day (3)
    group by
    	l_returnflag,
    	l_linestatus
    order by
    	l_returnflag,
    	l_linestatus;
    limit -1;

Posting Permissions

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