5 Replies - 406 Views - Last Post: 26 March 2019 - 09:00 AM

#1 juniordev101   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 22-March 19

SQL Not returning right values

Posted 26 March 2019 - 04:15 AM

So im working on a project that is using a protected database ie. i only have read access
It store the date_time as a String (annoying), so i have to parse the output
The following is my sql code

SELECT SUBSTRING(date_time,1,10), AVG(Total), sum(Total_ly),  AVG ((Total + Total_ly)/2) FROM Transaction WHERE Substring(date_time,1,10) between '25/02/2019' AND '26/03/2019' Group By date_time Having sum(Total)<>0;




It works fine except in the output its giving the following :

'25/02/2019', '2000', '2400', '2200'
'25/03/2019', '4500', '1570', '3035'
'26/02/2019', '4400', '3200', '3800'
'26/03/2019', '7.6', '0', '3.8'



Where i want it to give the output like :

25/02/2019
26/02/2019
25/03/2019
26/03/2019

so the months are together, any suggestions much appreciated.!

Is This A Good Question/Topic? 0
  • +

Replies To: SQL Not returning right values

#2 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6813
  • View blog
  • Posts: 28,189
  • Joined: 12-December 12

Re: SQL Not returning right values

Posted 26 March 2019 - 04:32 AM

This is nothing to do with Site Questions & Support, moving to Databases.

Which variant of SQL is this? SQL Server? MySQL?
Was This Post Helpful? 0
  • +
  • -

#3 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7471
  • View blog
  • Posts: 15,493
  • Joined: 16-October 07

Re: SQL Not returning right values

Posted 26 March 2019 - 04:37 AM

Your RDBMS undoubtedly has a way to convert a string to a date, at which point you'd want to sort by that. And, conversely, a way to format dates. However, date handling is vendor specific, so you need to be vendor specific.
Was This Post Helpful? 0
  • +
  • -

#4 juniordev101   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 22-March 19

Re: SQL Not returning right values

Posted 26 March 2019 - 08:47 AM

View Postbaavgai, on 26 March 2019 - 04:37 AM, said:

Your RDBMS undoubtedly has a way to convert a string to a date, at which point you'd want to sort by that. And, conversely, a way to format dates. However, date handling is vendor specific, so you need to be vendor specific.


Hi guys
Apologies im quite new to this whole process, its MYSQL by sunmicrosystems which i believe is the same as ORACLE
Was This Post Helpful? 0
  • +
  • -

#5 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15226
  • View blog
  • Posts: 60,944
  • Joined: 12-June 08

Re: SQL Not returning right values

Posted 26 March 2019 - 08:52 AM

Searching: "mysql convert string to date" seems to narrow down to a pretty specific function.

https://www.w3school...str_to_date.asp
http://www.mysqltuto...ql-str_to_date/
Was This Post Helpful? 0
  • +
  • -

#6 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6813
  • View blog
  • Posts: 28,189
  • Joined: 12-December 12

Re: SQL Not returning right values

Posted 26 March 2019 - 09:00 AM

Quote

Apologies im quite new to this whole process, its MYSQL by sunmicrosystems which i believe is the same as ORACLE


Just to note that it is owned chaperoned , I believe, by Oracle but is not the same as the Oracle RDBMS.

I'll move this to the MySQL subforum.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1