4

I have a table tine_by_day and I know how to use TO_CHAR function in oracle, but how to get same output if I use in MySQL?

Is there any conversion function in MySQL for TO_CHAR()?

I have already tried date_format instead to_char but I'm not getting sufficient results.

SELECT
    to_char(t.the_date,'mm-DD-YYYY') Date,
    SUM(sf7.unit_sales) UnitSales,
    SUM(sf7.store_sales) StoreSales,
    SUM(sf7.store_cost) StoreCost
FROM time_by_day t INNER JOIN sales_fact_1997 sf7 ON t.time_id=sf7.time_id
WHERE
    to_char(t.the_date,'YYYY-MM-DD')>='2012-01-01'
    AND
    to_char(t.the_date,'YYYY-MM-DD')<='2012-01-07'
GROUP BY t.the_date
ORDER BY t.the_date
sam140
  • 219
  • 1
  • 5
  • 27

3 Answers3

4

In SQL Server, you would typically use the convert() function, which is not nearly as convenient as to_char(). For your query, you only need it in the select clause:

SELECT convert(varchar(10), t.the_date, 110) as Date,
       SUM(sf7.unit_sales) as UnitSales,
       SUM(sf7.store_sales) as StoreSales,
       SUM(sf7.store_cost) as StoreCost
FROM time_by_day t INNER JOIN
     sales_fact_1997 sf7
     ON t.time_id = sf7.time_id
WHERE t.the_date >='2012-01-01' AND
      t.the_date <= '2012-01-07'
GROUP BY t.the_date
ORDER BY t.the_date;

SQL Server will normally treat the ISO standard YYYY-MM-DD as a date and do the conversion automatically. There is a particular internationalization setting that treats this as YYYY-DD-MM, alas. The following should be interpreted correctly, regardless of such settings (although I would use the above form):

WHERE t.the_date >= cast('20120101' as date) AND
      t.the_date <= cast('20120107' as date)

EDIT:

In MySQL, you would just use date_format():

SELECT date_format(t.the_date, '%m-%d-%Y') as Date,
       SUM(sf7.unit_sales) as UnitSales,
       SUM(sf7.store_sales) as StoreSales,
       SUM(sf7.store_cost) as StoreCost
FROM time_by_day t INNER JOIN
     sales_fact_1997 sf7
     ON t.time_id = sf7.time_id
WHERE t.the_date >= date('2012-01-01') AND
      t.the_date <= date('2012-01-07')
GROUP BY t.the_date
ORDER BY t.the_date;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • It sort of does. The only format that always works doesn't have hyphens, but I stick with the form with hyphens. In addition to my being a human being (and finding it more readable), this form is widely accepted across databases, even in Oracle using the `date` keyword. – Gordon Linoff Aug 11 '14 at 11:05
  • Got it Gordon.! Your update clarified me and the comments better n better! – Maheswaran Ravisankar Aug 11 '14 at 11:07
  • Thanx for your suitable response i m using only mysql – sam140 Aug 11 '14 at 11:09
0

Based on Gordons approach, but usign CHAR(10) instead of VARCHAR(10) since there's hardly a date not being returned with a length of 10...

SELECT convert(char(10), t.the_date, 110) as [Date],
   SUM(sf7.unit_sales) as UnitSales,
   SUM(sf7.store_sales) as StoreSales,
   SUM(sf7.store_cost) as StoreCost
FROM time_by_day t INNER JOIN
 sales_fact_1997 sf7
 ON t.time_id = sf7.time_id
WHERE t. the_date >='20120101' AND
  t.the_date <= '20120107'
GROUP BY t.the_date
ORDER BY t.the_date;

Edit: also changed the date format in the WHERE clause to be ISO compliant and therewith not affected by the setting of DATEFORMAT.

Lmu92
  • 952
  • 5
  • 5
0

Recommend you do this :

CAST($(STR) AS CHAR(40))
SkylerLin
  • 1