Literal Values, Data Types, and Type Casting in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this SELECT and Column Expressions
Builds toward Arithmetic and Comparison Expressions, BETWEEN, IN, and LIKE, CASE WHEN Expressions, UNION, UNION ALL, INTERSECT, EXCEPT
What is Type Casting in SQL?
Every value in SQL has a type, and that type determines what SQL can do with it.
When you write 48000 * 0.075, SQL already knows what it's working with: both values are numeric, so the multiplication works. But sometimes a value arrives as the wrong type. A date stored as text. A price stored as a string. SQL won't treat those as numbers or dates on its own. You have to tell it what type a value really is. The :: operator handles this — it's called a cast.
How do you cast a value to another type in SQL?
Think of :: as saying "treat this as." You put it after the value, then write the target type. A text string like '2025-06-30' is just characters until you add ::date:
SELECT '2025-06-30'::date AS contract_end_dateNow SQL treats it as a real date. The ::date cast is what makes that switch.
You can cast between many types. Text to number: '199.99'::numeric. Number to text: 42::text. These come up regularly when data is imported from spreadsheets or external systems where everything lands as a string.
What is the difference between :: and CAST() in SQL?
SQL also has a second casting syntax that does the same thing:
SELECT CAST('2024-09-01' AS date) AS launch_date:: is shorter and more common in PostgreSQL. CAST() is standard SQL and appears in other databases. Both produce the same result.
What is a typed literal like INTERVAL '30 days'?
SQL also has type-prefixed literals for some types. An interval — a duration of time — is written as INTERVAL '30 days'. The type name comes before the quoted value instead of after it.
SELECT '10000'::integer / 4 AS truncated_result,
'10000'::numeric / 4 AS decimal_resultWhy does integer division drop the decimal in SQL?
The one thing that trips people up: integer division drops the decimal.
Divide an integer by an integer and SQL returns an integer, cutting off anything after the decimal point. '10000'::integer / 4 returns 2500, not 2500.0. Cast to ::numeric first and you get the full decimal result. The same fix works whenever you need the fractional part of a division: make at least one side a decimal type.
Practice Type Casting in SQL
Brightlane's legal team uses a contract management system that stores expiry dates as plain text strings rather than calendar dates. The string '2025-06-30' needs to be returned as a proper date value so the system can perform date comparisons.
Write a query to convert that text string into a date.
Output:
- A single row with one column,
contract_end_date, typed as a date.
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution9 Type Casting practice problems
Write a query to convert that text string into a date.
Write a query to convert it.
Write a query to convert it.
Write a query to return the total annual cost for a 12-month subscription.
Write a query to return the equal quarterly share for one department.
Write a query to return the trial duration as an interval.
Write a query to return it as a calendar date.
Write a query to return the exact per-department budget.
Write a query to return how many whole people end up per group.
Start learning to practice all 9 Type Casting problems, with instant grading and mastery tracking.
Deeper guides on Type Casting
- casting a string to a date
::date for ISO strings, to_date() for everything else, and the day-first date Postgres reads as a month.
Common questions about Type Casting
What is the difference between the double colon and CAST?
Only the spelling. Both produce the same value from the same input, so you can use either wherever a cast is allowed. The double colon is shorter and the form you will meet most often in PostgreSQL code.
What happens when a cast fails?
The query stops with an error naming the type and the value it could not read, such as invalid input syntax for type integer. A cast is not a best-effort conversion, so PostgreSQL will not quietly hand back a zero or a null for text that is not a number.
Does casting a column change the data in the table?
No. A cast produces a new value in the result of that one query and leaves the stored column exactly as it was. Casting a numeric price to text gives you text in the output while the column itself is still numeric.
How do you stop integer division from dropping the decimals?
Cast one side to numeric before dividing. Dividing an integer by an integer produces an integer, and the fractional part is discarded rather than rounded, so nine divided by ten comes out as zero. Make either side numeric and the full result survives.
