Streamhub's product team is setting up the launch schedule for a new release. The go-live date is stored as the text string '2024-09-01' in the project management system.
Write a query to return it as a calendar date.
Output:
- A single row with one column,
launch_date, typed as a date.
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
CAST('2024-09-01' AS date) AS launch_dateThe shape
CAST('2024-09-01' AS date) is the standard-SQL spelling of the same conversion the :: operator performs. It takes the quoted launch date and returns it as a real date value the schedule system can compare and order against other dates.
Clause by clause
CAST('2024-09-01' AS date)reads the text literal on the left, parses it as a date in theYYYY-MM-DDformat PostgreSQL recognises, and returns the correspondingdatevalue. The function-call shape is explicit: the value goes inside the parens, the keywordASseparates it from the target type, and the type name closes the expression.AS launch_datelabels the output column. The firstASbelongs to the cast (it names the target type); this secondASbelongs to the column alias. They're the same keyword doing two different jobs in the same statement.
Why this and not '2024-09-01'::date
Both produce identical results. CAST('2024-09-01' AS date) is the standard SQL form — defined by the SQL specification, portable to MySQL, SQL Server, Oracle, and every other major database. '2024-09-01'::date is the PostgreSQL-specific shorthand: shorter to type, but specific to this engine.
The choice is stylistic, and most PostgreSQL codebases mix the two. The cast form reads more naturally inside a longer expression. The :: form is cleaner for short inline casts. What doesn't change between them is the parsing requirement: the text has to be a format PostgreSQL recognises as a date.
The trap
The trap isn't the cast itself. It's assuming the cast handles any date-looking string. PostgreSQL is strict about the format it accepts. '09/01/2024' is ambiguous: September 1st or January 9th? PostgreSQL won't guess based on locale. If the project management system delivers dates in a non-YYYY-MM-DD form, the cast either errors or, in some formats, silently parses to the wrong day. Inspect the source format first and confirm the cast produces the date the prompt actually means.
You practiced converting text to a date — same outcome as the :: cast. Both CAST(... AS date) and '...'::date are valid; the choice is stylistic, not behavioral.
