6 Replies - 1415 Views - Last Post: 26 June 2012 - 06:11 PM Rate Topic: -----

#1 rosepetalpowder  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 65
  • Joined: 28-February 12

Oracle SQL statements - explain - what does this mean

Posted 26 June 2012 - 04:03 PM

 SELECT last_name, hire_date,
       TO_CHAR(hire_date, 'DAY') DAY
FROM     employees
ORDER BY TO_CHAR(hire_date - 1, 'd'); 


What is the
 ORDER BY TO_CHAR(hire_date - 1, 'd') 
mean?
Is This A Good Question/Topic? 0
  • +

Replies To: Oracle SQL statements - explain - what does this mean

#2 denting5  Icon User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 84
  • Joined: 03-June 12

Re: Oracle SQL statements - explain - what does this mean

Posted 26 June 2012 - 04:49 PM

That last line is an instruction to list items found in the employees database by hire_date, a function.
Was This Post Helpful? 1
  • +
  • -

#3 rosepetalpowder  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 65
  • Joined: 28-February 12

Re: Oracle SQL statements - explain - what does this mean

Posted 26 June 2012 - 05:44 PM

View Postdenting5, on 26 June 2012 - 04:49 PM, said:

That last line is an instruction to list items found in the employees database by hire_date, a function.



wht does
 hiredate - 1, d 
mean?

This post has been edited by rosepetalpowder: 26 June 2012 - 05:44 PM

Was This Post Helpful? 0
  • +
  • -

#4 denting5  Icon User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 84
  • Joined: 03-June 12

Re: Oracle SQL statements - explain - what does this mean

Posted 26 June 2012 - 05:50 PM

hire_date is a data set incorporated into the function, likely consisting of a numerical value. That minus one, along with the day (not totally certain about how the day is incorporated into this, just know that it is in the function) are put together to form the function used to determine the order in which the data sets in employees are listed.
Was This Post Helpful? 0
  • +
  • -

#5 fromTheSprawl  Icon User is offline

  • Monomania
  • member icon

Reputation: 513
  • View blog
  • Posts: 2,056
  • Joined: 28-December 10

Re: Oracle SQL statements - explain - what does this mean

Posted 26 June 2012 - 05:56 PM

It probably means that the days when passed to the SQL are in numerical form like maybe

1 - Monday
2 - Tuesday
3 - Wednesday

Then since the database probably uses 0 - index you need to get day - 1 to get the correct day.

'd' - probably means notation of the days as one or two characters for representing days such as:

M - Monday
T - Tuesday
W - Wednesday

Though in Java (and in SQLDeveloper) we use 'dd' so you can distinguish between Tuesday and Thursday.

'Day' will probably return the full word of the day. :)
Was This Post Helpful? 1
  • +
  • -

#6 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3712
  • View blog
  • Posts: 5,963
  • Joined: 08-June 10

Re: Oracle SQL statements - explain - what does this mean

Posted 26 June 2012 - 05:58 PM

As I understand this, the TO_CHAR(hire_date - 1, 'd') function, assuming the hire_date column is in a datetime format, should return the number of the weekday (1-7) for the day preceding the value in the column.

To break it down, the hire_date - 1 expression will return the date value minus one day. The TO_CHAR function, when passed a datetime value and the "D" template, will return the number of the weekday for that date.
Was This Post Helpful? 1
  • +
  • -

#7 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3712
  • View blog
  • Posts: 5,963
  • Joined: 08-June 10

Re: Oracle SQL statements - explain - what does this mean

Posted 26 June 2012 - 06:11 PM

View Postdenting5, on 27 June 2012 - 12:50 AM, said:

hire_date is a data set incorporated into the function, likely consisting of a numerical value.

Why do you say that? Judging by it's use in the column list earlier in the query, and the "d" in the second parameter of the function, I think it far more likely to be a datetime value.


View PostfromTheSprawl, on 27 June 2012 - 12:56 AM, said:

'd' - probably means notation of the days as one or two characters for representing days such as:

According to the docs, it actually represents the weekday as a number between 1 and 7.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1