Problem in creating a date column using string methods in sql

OneLearn Course SQL
,
#1

image

i am trying to solve this question in 2 ways, in first one “date” column is of type ‘varchar’

,and in the 2nd ,“cleaned_date” column is of ‘timestamp’ type.What am i doing wrong please specify,
Both are giving errors,for first one error is:
ERROR: date/time field value out of range: “01/31/2014”
HINT: Perhaps you need a different “datestyle” setting.
SQL state: 22008
and for 2nd the error is:
ERROR: function left(timestamp without time zone, integer) does not exist
LINE 1: SELECT LEFT(“cleaned_date”,10)::varchar AS new_date
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 8

#2

This code is not working because cleaned_date column is a timestamp column and not string column so LEFT function won’t work.

SELECT LEFT(cleaned_date,10)::varchar AS new_date 
  FROM tutorial.sf_crime_incidents_cleandate sf_table

This is what the error is also saying. left(timestamp without time zone, integer) does not exist i.e. left function with timestamp does not exist means it will not work with timestamp

#3

The first code is working when i ran it. The result is shown below.

image
image1166×1188 48.4 KB

#4

okay i got the reason for 2nd one but for the 1st one my code is still showing the same error:

ERROR: date/time field value out of range: “01/31/2014”
HINT: Perhaps you need a different “datestyle” setting.
SQL state: 22008

for the same reason i am not able to do question.8 also

#5

Try to run this before running your query

SET datestyle = dmy;
#6

This is giving output as ‘SET’ , But the error is still there

#7

using the following command i am able to run the query but format is dd-mm-yyyy and not what is asked in the question:

SET datestyle =US, MDY;

SELECT LEFT(“date”,10)::DATE AS new_date
FROM tutorial.sf_crime_incidents_cleandate sf_table