How to convert DateTime to VarChar

sql

datetime-conversion

sql-functions

date-formatting

byAlex Kataev·Sep 15, 2024

Quickly transform DateTime into VarChar using the mighty CONVERT() function:

SELECT CONVERT(VARCHAR, YourDateTimeColumn, 120) -- Exquisite Output: yyyy-mm-dd hh:mi:ss

Or, tailor your formats with a stroke of FORMAT() in SQL Server 2012+:

SELECT FORMAT(YourDateTimeColumn, 'yyyy-MM-dd HH:mm:ss') -- The world is your oyster, format as you need!

Replace our fancy YourDateTimeColumn placeholder with your actual date-time column.

If your taste is simply date, no time:

SELECT CONVERT(VARCHAR(10), YourDateTimeColumn, 23) -- Just a plain date: yyyy-mm-dd, no bells and whistles

And, if you just want to be a Scrooge and omit time:

SELECT LEFT(CONVERT(VARCHAR, YourDateTimeColumn, 120), 10) -- Pinching pennies, leaving us with: yyyy-mm-dd

Digging into the Convert function

Let's shovel into CONVERT() and uncover some hidden gems:

Outfit your conversion with style 23 for a clean, classic look:

SELECT CONVERT(VARCHAR, YourDateTimeColumn, 23) -- Voila! A timeless date: yyyy-mm-dd

Don a monocle and peruse Microsoft's Docs for a comprehensive style guide:

- Styles range from `0` to `131`, including ISO (`120`, `121`) and locale-specific (`130`, `131`).
- Experiment like a mad scientist to see how each style morphs your output.

For a minimalist result, use NVARCHAR(11) to skip time:

SELECT CONVERT(NVARCHAR(11), YourDateTimeColumn, 23) -- No time for time, just a date!

Avoiding slipping on banana peels

A few footings to beware on this path:

Watch your step with potential landmines known as invalid styles:

- Not all style numbers are as sweet as pie. Refer to **official documentation** to avoid a rotten apple.
- Testing saves the day! Test your styles before broadcasting them on the SQL airwaves.

Don't forget that your choice of conversion can stretch or squish your data type lengths:

- `VARCHAR(10)` fits just the date; the `10` is a corset, keeping the time at bay.
- For full `datetime`, bust out the `VARCHAR(23)`. Makes room for seconds and milliseconds!

Precision is the key to SQL domination

Let's refine our SQL craftmanship:

Picking the right format

Don't just order random formats off the menu:

Feast on the globally acclaimed ISO standard:

SELECT CONVERT(VARCHAR, YourDateTimeColumn, 120) 
-- An international delicacy bound by ISO standards

Savour the local flavours with styles 130 and 131:

SELECT CONVERT(VARCHAR, YourDateTimeColumn, 130) 
-- An SQL eclair baked with locale-specific ingredients

Trimming time like an SQL barber

Keep just the date and shave off the time:

Get buzz cuts with LEFT trim:

SELECT LEFT(CONVERT(VARCHAR, YourDateTimeColumn, 120), 10) 
-- The SQL barber trimming time off our datetime

Craft precision formats with limited lengths:

SELECT CONVERT(VARCHAR(10), YourDateTimeColumn, 20) 
--Same barber, exacting precision with our date's style

Style experimentation lab

Try on different style numbers like changing rooms:

SELECT CONVERT(VARCHAR, YourDateTimeColumn, style_number) FROM (VALUES (0), (1), (100), (101), (120), (121), (130), (131)) AS Styles(style_number) 
-- SQL's version of wardrobe trials