Converting yyyy-mm-dd to integer representation in a database(Codefight Task)

Related searches

I am new to SQL and have literately hit the rocks now on a codefight database task. I would appreciate any help and a little bit of explanation. Here is the task(Clearer if you follow the link):

Your nephews Huey, Dewey, and Louie are staying with you over the winter holidays. Ever since they arrived, you've hardly had a day go by without some kind of incident - the little rascals do whatever they please! Actually, you're not even mad; the ideas they come up with are pretty amazing, and it looks like there's even a system to their mischief.

You decided to track and analyze their behaviour, so you created the mischief table in your local database. The table has the following columns:

mischief_date: the date of the mischief (of the date type); author: the nephew who caused the mischief ("Huey", "Dewey" or "Louie"); title: the title of the mischief. It looks like each of your nephews is active on a specific day of the week. You decide to check your theory by creating another table as follows: The resulting table should contain four columns, weekday, mischief_date, author, and title, where weekday is the weekday of mischief_date (0 for Monday, 1 for Tuesday, and so on, with 6 for Sunday). The table should be sorted by the weekday column, and for each weekday Huey's mischief should go first, Dewey's should go next, and Louie's should go last. In case of a tie, mischief_date should be a tie-breaker. If there's still a tie, the record with the lexicographically smallest title should go first.

It is guaranteed that all entries of mischief are unique.

Example

For the following table mischief

+---------------+--------+----------------------------------+
| mischief_date | author |              title               |
+---------------+--------+----------------------------------+
| 2016-12-01    | Dewey  | Cook the golden fish in a bucket |
| 2016-12-01    | Dewey  | Paint the walls pink             |
| 2016-12-01    | Huey   | Eat all the candies              |
| 2016-12-01    | Louie  | Wrap the cat in toilet paper     |
| 2016-12-08    | Louie  | Play hockey on linoleum          |
| 2017-01-01    | Huey   | Smash a window                   |
| 2017-02-06    | Dewey  | Create a rink on the porch       |
+---------------+--------+----------------------------------+

the output should be

+---------+---------------+--------+----------------------------------+
| weekday | mischief_date | author |              title               |
+---------+---------------+--------+----------------------------------+
|       0 | 2017-02-06    | Dewey  | Create a rink on the porch       |
|       3 | 2016-12-01    | Huey   | Eat all the candies              |
|       3 | 2016-12-01    | Dewey  | Cook the golden fish in a bucket |
|       3 | 2016-12-01    | Dewey  | Paint the walls pink             |
|       3 | 2016-12-01    | Louie  | Wrap the cat in toilet paper     |
|       3 | 2016-12-08    | Louie  | Play hockey on linoleum          |
|       6 | 2017-01-01    | Huey   | Smash a window                   |
+---------+---------------+--------+----------------------------------+

The first and the eighth of December are Thursdays, the sixth of February is a Monday, and the first of January is a Sunday.

The dates in the example are given in the format YYYY-MM-DD.

Here is my code:

/*Please add ; after each select statement*/

CREATE PROCEDURE mischievousNephews()


BEGIN
     DAYNAME(mischief_date) as weekday
     declare @iweekdayn INT
     select @iweekdayn = case weekday
     WHEN 'Sunday' THEN 6
     WHEN 'Monday' THEN 0
     WHEN 'Tuesday' THEN 1
     WHEN 'Wednesday' THEN 2
     WHEN 'Thursday' THEN 3
     WHEN 'Friday' THEN 4
     WHEN 'Saturday' THEN ;


     select @iweekdayn , mischief_date,author,title from mischief order by 
     wekdayn;


END

And I have this result:

mischief

+---------------+--------+----------------------------------+
| mischief_date | author |              title               |
+---------------+--------+----------------------------------+
| 2016-12-01    | Dewey  | Cook the golden fish in a bucket |
| 2016-12-01    | Dewey  | Paint the walls pink             |
| 2016-12-01    | Huey   | Eat all the candies              |
| 2016-12-01    | Louie  | Wrap the cat in toilet paper     |
| 2016-12-08    | Louie  | Play hockey on linoleum          |
| 2017-01-01    | Huey   | Smash a window                   |
| 2017-02-06    | Dewey  | Create a rink on the porch       |
+---------------+--------+----------------------------------+

Output:

+----------+---------------+--------+----------------------------------+
| weekday  | mischief_date | author |              title               |
+----------+---------------+--------+----------------------------------+
| Monday   | 2017-02-06    | Dewey  | Create a rink on the porch       |
| Sunday   | 2017-01-01    | Huey   | Smash a window                   |
| Thursday | 2016-12-01    | Dewey  | Cook the golden fish in a bucket |
| Thursday | 2016-12-01    | Dewey  | Paint the walls pink             |
| Thursday | 2016-12-01    | Huey   | Eat all the candies              |
| Thursday | 2016-12-01    | Louie  | Wrap the cat in toilet paper     |
| Thursday | 2016-12-08    | Louie  | Play hockey on linoleum          |
+----------+---------------+--------+----------------------------------+

Expected Output:

+---------+---------------+--------+----------------------------------+
| weekday | mischief_date | author |              title               |
+---------+---------------+--------+----------------------------------+
|       0 | 2017-02-06    | Dewey  | Create a rink on the porch       |
|       3 | 2016-12-01    | Huey   | Eat all the candies              |
|       3 | 2016-12-01    | Dewey  | Cook the golden fish in a bucket |
|       3 | 2016-12-01    | Dewey  | Paint the walls pink             |
|       3 | 2016-12-01    | Louie  | Wrap the cat in toilet paper     |
|       3 | 2016-12-08    | Louie  | Play hockey on linoleum          |
|       6 | 2017-01-01    | Huey   | Smash a window                   |
+---------+---------------+--------+----------------------------------+

Well, you could simply use the WEEKDAY function, here is the documentation:

https://msdn.microsoft.com/pt-br/library/ee634550(v=sql.120).aspx

With return type = 3 it'll return the mondays has zero (0), tuesdays as 1 and so on...

After that all you need is a good old fashioned ORDER BY to have the order correct for the exercise:

ORDER BY weekday,mischief_date,title

Recently Active Questions - Page 64520, Converting yyyy-mm-dd to integer representation in a database(Codefight Task) � sql � May 10 '19 at 5:34 Arun Kumar N. 2. 1� Convert date yyyy-mm-dd to integer YYYYMM. Ask Question Asked 5 years, 1 month ago. Thanks for contributing an answer to Database Administrators Stack Exchange!

You should look for the function WEEKDAY(date), in MySQL doesn't need to be specified the return_type, it will return the weekday from Monday to Sunday starting by 0. Also, in the ORDER BY check the function FIELD(column, 'Value_1', 'Value_2', ..., 'Value_x') for the specific order of the nephews name. Don't forget the rest of the tie-breakers or your answer will be incomplete. Good luck duck!

Convert date yyyy-mm-dd to integer YYYYMM, On version 2012 or higher you can use the format function to get just year and month, then cast it as an int. On versions prior to 2012 you can do the formatting� "Conversion" includes, but is not limited to, casting and coercion. Casting is explicit conversion and uses the CAST() function. Coercion is implicit conversion, which BigQuery performs automatically under the conditions described below. There is a third group of conversion functions that have their own function names, such as UNIX_DATE().

The following query working fine. I have tried with the CodeSignal already!

CREATE PROCEDURE mischievousNephews()
BEGIN
    SELECT WEEKDAY(mischief_date) AS weekday,mischief_date,author,title
    FROM mischief ORDER BY weekday, FIELD(author,"Huey","Dewey","Louie"),mischief_date,title;
END

Hope! it helps you! Thank you!

Data Types — CUBRID 9.2.0 documentation, The SMALLINT data type is used to represent a small integer type. represented as double precision (in 15 significant figures) and it is converted into DOUBLE The date value is displayed in the type of 'MM/DD/YYYY' in CSQL, and it is Therefore, if storage of a LOB type is located on the local machine, no tasks on the� Learn about expression functions in mapping data flow. Data transformation expressions in mapping data flow. 02/15/2019; 36 minutes to read

Reactjs Calculate Sum, Answer: To convert a string to a number, use the JavaScript functions Given an array of integers, find the sum of its elements. r/reactjs: A community for most commonly used charts. ; Otherwise, we can represent pow(x, n) as x * pow(x, n - 1). date MM DD YYYY Today is: June 24, 2020 Calculate the date some number� ToBoolean(SByte) Converts the value of the specified 8-bit signed integer to an equivalent Boolean value. ToBoolean(String, IFormatProvider) Converts the specified string representation of a logical value to its Boolean equivalent, using the specified culture-specific formatting information.

Python Program For Dirac Delta Function, if it represents before midday and 'hh:mm p. here we represent our function as σ. FilterPy is a Python library that implements a number of Bayesian filters, most notably That allows us to do the Laplace transform of the Dirac Delta function. By default now() function returns output in YYYY-MM-DD HH:MM:SS:MS format . Hi, Using: PowerCenter 7.1.5 Database : Oracle 8i Source table has a column (SOURCE_COL) with string data type but has date values in it, in this format '09-MAR-2007' Target table has a column (TARGET_COL) with date data type.

The field is TRANDATE and the data is stored as decimal(9, 0). I have written a SQL view to join a few tables so that we can use the view to pull data into microsoft excel with user friendly field names for analysis. I am looking for a way to convert the dates so that they display as MM/DD/YYYY instead of the YYYYMMDD format.

Comments
  • Dear Donald, please read How to Ask and edit your question. keep it short and well formatted. Thanks.
  • Look at datename function for the weekday name, and datepart for the numeric
  • Which DBMS are you using?