Try this
Cast('7/7/2011' as datetime)
and
Convert(DATETIME, '7/7/2011', 101)
See CAST and CONVERT (Transact-SQL) for more details.
Answer from gauravg on Stack OverflowMicrosoft Learn
learn.microsoft.com › en-us › sql › t-sql › functions › cast-and-convert-transact-sql
CAST and CONVERT (Transact-SQL) - SQL Server | Microsoft Learn
An integer expression that specifies how the CONVERT function will translate expression. For a style value of NULL, NULL is returned. data_type determines the range. Returns expression, translated to data_type. For a date or time data type expression, style can have one of the values shown in the following table.
MSSQLTips
mssqltips.com › home › sql date format examples using convert function
SQL Date Format Examples using SQL CONVERT Function
September 26, 2025 - Learn how to use SQL CONVERT for different SQL date format options and achieve the desired date representation.
Videos
09:05
SQL | How to Convert Date / Time Formats ? | Convert | Format - ...
13:44
MS SQL tutorial on CONVERT and CAST functions - YouTube
08:36
How to Convert VARCHAR to Date in SQL Server - YouTube
28:31
59. DATE Format in SQL - Use SQL Convert with Date Format in SQL ...
06:45
Convert numbers year, month and days and more to dates in SQL Server ...
Working With SQL Server Dates | Format Function, Culture ...
W3Schools
w3schools.com › sql › func_sqlserver_convert.asp
SQL Server CONVERT() Function
String Functions: Asc Chr Concat ... Plan SQL Bootcamp SQL Certificate SQL Training ... The CONVERT() function converts a value (of any type) into a specified datatype....
Medium
medium.com › towardsdev › how-to-use-the-convert-function-for-datetime-conversion-in-ms-sql-server-f480cdc885f5
How to Use the CONVERT() Function for DateTime Conversion in MS ...
October 21, 2024 - In this example, the 101 style converts the date to the MM/DD/YYYY format.
Top answer 1 of 16
368
Try this
Cast('7/7/2011' as datetime)
and
Convert(DATETIME, '7/7/2011', 101)
See CAST and CONVERT (Transact-SQL) for more details.
2 of 16
62
Run this through your query processor. It formats dates and/or times like so and one of these should give you what you're looking for. It wont be hard to adapt:
Declare @d datetime
select @d = getdate()
select @d as OriginalDate,
convert(varchar,@d,100) as ConvertedDate,
100 as FormatValue,
'mon dd yyyy hh:miAM (or PM)' as OutputFormat
union all
select @d,convert(varchar,@d,101),101,'mm/dd/yy'
union all
select @d,convert(varchar,@d,102),102,'yy.mm.dd'
union all
select @d,convert(varchar,@d,103),103,'dd/mm/yy'
union all
select @d,convert(varchar,@d,104),104,'dd.mm.yy'
union all
select @d,convert(varchar,@d,105),105,'dd-mm-yy'
union all
select @d,convert(varchar,@d,106),106,'dd mon yy'
union all
select @d,convert(varchar,@d,107),107,'Mon dd, yy'
union all
select @d,convert(varchar,@d,108),108,'hh:mm:ss'
union all
select @d,convert(varchar,@d,109),109,'mon dd yyyy hh:mi:ss:mmmAM (or PM)'
union all
select @d,convert(varchar,@d,110),110,'mm-dd-yy'
union all
select @d,convert(varchar,@d,111),111,'yy/mm/dd'
union all
select @d,convert(varchar,@d,12),12,'yymmdd'
union all
select @d,convert(varchar,@d,112),112,'yyyymmdd'
union all
select @d,convert(varchar,@d,113),113,'dd mon yyyy hh:mm:ss:mmm(24h)'
union all
select @d,convert(varchar,@d,114),114,'hh:mi:ss:mmm(24h)'
union all
select @d,convert(varchar,@d,120),120,'yyyy-mm-dd hh:mi:ss(24h)'
union all
select @d,convert(varchar,@d,121),121,'yyyy-mm-dd hh:mi:ss.mmm(24h)'
union all
select @d,convert(varchar,@d,126),126,'yyyy-mm-dd Thh:mm:ss:mmm(no spaces)'
DotFactory
dofactory.com › sql › convert-datetime-to-date
SQL Convert DATETIME to DATE
GETDATE returns the current database server's datetime. CONVERT converts the datetime value to a date format.
GeeksforGeeks
geeksforgeeks.org › sql › sql-query-to-convert-datetime-to-date
SQL Query to Convert DateTime to Date in SQL Server - GeeksforGeeks
Aggregating by Date: For example, when calculating daily totals or averages, you may want to group data by date only, ignoring the time part. The aim of this article data is to convert DateTime to Date in SQL Server like YYYY-MM-DD HH:MM: SS to YYYY-MM-DD.
Published July 23, 2025
Experts Exchange
experts-exchange.com › articles › 12315 › SQL-Server-Date-Styles-formats-using-CONVERT.html
SQL Server Date Styles (formats) using CONVERT() | Experts Exchange
October 3, 2013 - DELIMITED STARTS PATTERN STYLED DATE SYNTAX STYLE LENGTH YYYY YYYY MM DD 20010223 convert(varchar, your_data_here ,112) 112 8 YY YY MM DD 010223 convert(varchar, your_data_here ,12) 12 6 slash YYYY YYYY MM DD 2001/02/23 convert(varchar, your_data_here ,111) 111 10 slash YY YY MM DD 01/02/23 convert(varchar, your_data_here ,11) 11 8 slash MM MM DD YYYY 02/23/2001 convert(varchar, your_data_here ,101) 101 10 slash MM MM DD YY 02/23/01 convert(varchar, your_data_here ,1) 1 8 slash DD DD MM YYYY 23/02/2001 convert(varchar, your_data_here ,103) 103 10 slash DD DD MM YY 23/02/01 convert(varchar, you
SQL Shack
sqlshack.com › sql-convert-date-functions-and-formats
SQL Convert Date functions and formats
May 21, 2021 - Syntax for the SQ: CONVERT() function is as follows. Data_Type: We need to define data type along with length. In the date function, we use Varchar(length) data types
DB Vis
dbvis.com › thetable › extracting-time-and-date-in-ms-sql-server-a-comprehensive-guide
Extract Date and Time in MS SQL Server: A Complete Guide
February 7, 2025 - This will return the time portion in the format hh:mm:ss AM/PM. If the datetime value is 2023-3-01 11:50:05.627, the result will be 11:50:05 AM. The above query first converts the datetime value to a string using style 109, which includes the date and time in the format mon dd yyyy hh:mi:ss:mmmAM.
SQL Easy
sql-easy.com › learn › how-to-convert-datetime-to-date-in-sql-server
How to Convert DATETIME to DATE in SQL Server Effortlessly - SQL Knowledge Center
February 9, 2024 - SELECT CAST(datetime_column AS DATE) FROM table_name; This method is straightforward and widely supported across different SQL databases. It converts the datetime value into a date, discarding the time part.
Quest Blog
blog.quest.com › home › various ways to use the sql convert date function
Various ways to use the SQL CONVERT date function
April 26, 2024 - DECLARE @InputDate DATETIME = '2020-12-08 15:58:17.643' SELECT 'd' AS [FormatCode], 'Short Date Pattern' AS 'Pattern', Format(@InputDate, 'd') AS 'Output' UNION ALL SELECT 'D' AS [FormatCode], 'Long Date Pattern' AS 'Pattern', Format(@InputDate, 'D') AS 'Output' UNION ALL SELECT 'f' AS [FormatCode], 'Full Date/Time pattern (Short Time)' AS 'Pattern', Format(@InputDate, 'f') AS 'Output' UNION ALL SELECT 'F' AS [FormatCode], 'Full Date/Time pattern (Long Time)' AS 'Pattern', Format(@InputDate, 'F') UNION ALL SELECT 'g' AS [FormatCode], 'General Date/Time pattern (Short Time)' AS 'Pattern', Forma
SQL Server Tutorial
sqlservertutorial.net › home › sql server system functions › convert datetime to date
Convert Datetime to Date in SQL Server By Practical Examples
April 11, 2020 - To convert a datetime to a date, you can use the CONVERT(), TRY_CONVERT(), or CAST() function.
MSSQLTips
mssqltips.com › home › sql convert date to yyyymmdd
SQL Convert Date to YYYYMMDD
May 27, 2025 - In this tutorial I’ll set up a test table of date values with various data types: DATE, DATETIME, CHAR(8) then load sample date and show examples of outputs and filtering. I will use the CAST and CONVERT functions for date conversions in Microsoft SQL Server.
Top answer 1 of 10
203
The following query will get the current datetime and convert into string. with the following format yyyy-mm-dd hh:mm:ss(24h)
SELECT convert(varchar(25), getdate(), 120)
- SQLFiddle Demo
- SQL Server Date Formats
2 of 10
43
There are many different ways to convert a datetime to a string. Here is one way:
SELECT convert(varchar(25), getdate(), 121) – yyyy-mm-dd hh:mm:ss.mmm
See Demo
Here is a website that has a list of all of the conversions:
How to Format datetime & date in SQL Server
Microsoft Learn
learn.microsoft.com › en-us › sql › t-sql › data-types › date-transact-sql
date (Transact-SQL) - SQL Server | Microsoft Learn
Otherwise, a runtime error is raised. Implicit conversions or explicit conversions that don't specify a style, from date and time types to string literals, are in the default format of the current session. The following table shows the rules for converting a string literal to the date data type.
Database Star
databasestar.com › convert-datetime-to-date
How to Convert DATETIME to DATE in SQL Server | Database Star: Home
July 2, 2020 - But what if you only want to see the date (day, month, and year) and none of the time component? There are many ways you can do this. You can convert a DATETIME to a DATE using the CONVERT function.
Stack Overflow
stackoverflow.com › questions › 74053120 › sql-server-convert-datetime-to-yyyy-mm-ddthhmmss-sssz
Sql server convert datetime to ''YYYY-MM-DD'T'HH:mm:ss.SSS'Z" - Stack Overflow
Select Convert(varchar,start_date,126)+'.000Z' as required_date from Table1 Select Convert(varchar,start_date,127)+'.000Z' as required_date from Table1
Top answer 1 of 3
4
You should use the right format, in your case it would be:
CONVERT(date, [PublishedDate], 104) AS PublishedDate
Also, once it's in a date, datetime or other date datatype, it doesn't have a format at all.
edit: Once you have your values in a date datatype, of course you can recast to a varchar to get the visual representation of the date you need.
edit2: If you want a date datatype, you should convert to date: CONVERT(DATE, [your column], [your format]).
If you want a nvarchar datatype, you should convert to nvarchar: CONVERT(nvarchar(x), [your column], [your format]).
You have an nvarchar that you want to display in a certain format, so you should first convert to date, then back to varchar (I doubt you need unicode):
CONVERT(VARCHAR(10), CONVERT(date, [PublishedDate], 104), 126)
The 104 you have to change for columns that are currently in a different format.
The best solution by far, is to change the datatypes to date. That is a bit of work, but definitely worthwhile.
2 of 3
1
If you really want the string do like this:
CONVERT(char(10), CONVERT(date,[date], 104),126) AS PublishedDate
Convert the string to date, using the 104 format (dd.mm.yyyy), as it is your original format, then convert the date into string, using the 126 format (yyy-mm-dd)
DB Vis
dbvis.com › thetable › the-ultimate-guide-to-the-sql-server-date-format
The Ultimate Guide to the SQL Server Date Format
October 23, 2024 - This calls the GETDATE() function to retrieve the current date and time, and then uses an SQL cast to convert the result to DATE.