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.
Microsoft Learn
learn.microsoft.com › en-us › sql › t-sql › functions › cast-and-convert-transact-sql
CAST and CONVERT (Transact-SQL) - SQL Server | Microsoft Learn
This includes xml, bigint, and sql_variant. Alias data types can't be used. An optional integer that specifies the length of the target data type, for data types that allow a user specified length. The default value is 30. 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 ...
W3Schools
w3schools.com › sql › func_sqlserver_convert.asp
SQL Server CONVERT() Function
String Functions: ASCII CHAR_LENGTH ... STR_TO_DATE SUBDATE SUBTIME SYSDATE TIME TIME_FORMAT TIME_TO_SEC TIMEDIFF TIMESTAMP TO_DAYS WEEK WEEKDAY WEEKOFYEAR YEAR YEARWEEK Advanced Functions: BIN BINARY CASE CAST COALESCE CONNECTION_ID CONV CONVERT CURRENT_USER DATABASE IF IFNULL ISNULL LAST_INSERT_ID NULLIF SESSION_USER SYSTEM_USER USER VERSION SQL Server ...
t sql - Convert to DATE T-SQL - Stack Overflow
I have a field with date in the format dd.mm.yyyy E.x. 29.05.2016. I want to SELECT it as DATE but I get an error when I try the following: ... Conversion failed when converting date and/or time from character string. More on stackoverflow.com
sql server - Converting the date and time column into an SQL format date and time - Stack Overflow
I want to update/convert a string of date and time, into a SQL Date and time format, i.e it would look exactly the same, but it would not read as a nvarchar, but as date and time. Date More on stackoverflow.com
Converting date format in sqlserver
When SQL store date, it is not using any format at all. So, you do not store date in SQL by any format - only displays based on the local set of your server. If you want to show dd/mm/yyyy, use the Format method or Convert / Cast on the Date field to change the format. More on learn.microsoft.com
4
0
CONVERT to Date
Hi. I've got a field that I'm trying to report on. It's set as a string in the database but formatted as short date. When I try report it throws out this error Conversion failed when converting the varchar value '10/09/2024 00:00:00' to data type int. What I'm trying to do is return all records ... More on forums.sqlteam.com
1
0
March 25, 2021Videos
00:48
Convert datetime to date in SQL - YouTube
09:05
SQL | How to Convert Date / Time Formats ? | Convert | Format - ...
13:44
MS SQL tutorial on CONVERT and CAST functions - YouTube
04:18
Convert Datetime into String | Datetime to String conversion in ...
12:15
Converting a String to Date in SQL | CAST | CONVERT | TRY_CAST ...
How to Format Dates in SQL Server using Convert and Format
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
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.
InfluxData
influxdata.com › home › how does date conversion work in sql? | influxdata
How Does Date Conversion Work in SQL? | InfluxData
October 6, 2023 - So, a SQL database with a date and time of "08/15/2023 23:20:30" can be read as invalid for a format of "DD/MM/YYYY" as there is no "15" month in the Gregorian calendar. --Wrong format Declare @date_time_value varchar(100)= '08/15/2023 23:20:30' select CONVERT(datetime2, @date_time_value, 103) as Date;
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
Mimo
mimo.org › glossary › sql › convert-function
SQL CONVERT Function: Syntax, Usage, and Examples
Convert both sides to DATE: ... You can use this to ignore the time portion entirely and match just the day. The CONVERT function returns NULL if the input can’t be converted to the desired type. That means no error, but you might lose rows unexpectedly. ... TRY_CONVERT gives you a safer way to handle potential conversion failures without crashing your query. Using the SQL CONVERT function gives you precision and control over how data appears and interacts in your queries.
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)
GeeksforGeeks
geeksforgeeks.org › sql › sql-query-to-convert-datetime-to-date
SQL Query to Convert DateTime to Date in SQL Server - GeeksforGeeks
Explanation: Here, the CAST() function is used to convert a string representation of DateTime to a Date, removing the time component. The CONVERT() function is another SQL Server function that allows you to convert data types.
Published July 23, 2025
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 - Please note that the number of time formats available in SQL Server is more limited compared to date formats. However, you can still use custom formatting techniques to display time in the desired format. For example, if you want to display the time in a 12-hour format with an AM/PM indicator, you can use the following query: ... 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.
Stack Overflow
stackoverflow.com › questions › 58471662 › converting-the-date-and-time-column-into-an-sql-format-date-and-time
sql server - Converting the date and time column into an SQL format date and time - Stack Overflow
If I may hazard a guess, it is giving you the problem because it is expecting date in format MM-dd-yyyy, but 20 in your date is not a valid month. Try working around that. ... You can convert date by style 105.
freeCodeCamp
freecodecamp.org › news › sql-convert-the-date-to-string-or-datetime-function-2
SQL CONVERT – The DATE to String or DATETIME Function
January 10, 2023 - You need to be able to work with ... you how to convert a date and datetime to a string in SQL with the CONVERT() and STR_TO_DATE() functions....
Microsoft Learn
learn.microsoft.com › en-us › answers › questions › 1425205 › converting-date-format-in-sqlserver
Converting date format in sqlserver - Microsoft Q&A
So, you do not store date in SQL by any format - only displays based on the local set of your server. If you want to show dd/mm/yyyy, use the Format method or Convert / Cast on the Date field to change the format.
DotFactory
dofactory.com › sql › convert-datetime-to-string
SQL Convert DATETIME to String
SQL String to Datetime · Converting a datetime to a string value is a common requirement. Use CONVERT or TRY_CONVERT to perform this operation. This example converts the current datetime to a string. SELECT CONVERT(VARCHAR, GETDATE()) AS String Try it live ·
SQL Shack
sqlshack.com › sql-convert-date-functions-and-formats
SQL Convert Date functions and formats
May 21, 2021 - We do face many such scenarios when we do not have a date format as per our requirement. We cannot change table properties to satisfy each requirement. In this case, we need to use the built-in functions in SQL Server to give the required date format. We have the following SQL convert date and Time data types in SQL Server.
DotFactory
dofactory.com › sql › convert-datetime-to-date
SQL Convert DATETIME to DATE
SQL DateTime to String · Converting a datetime to a date value is a common requirement. Three functions are available: CONVERT, TRY_CONVERT, and CAST. This example converts the current datetime to a date. SELECT CONVERT(DATE, GETDATE()) AS Date Try it live ·
GeeksforGeeks
geeksforgeeks.org › sql › sql-query-to-convert-date-to-datetime
SQL Query to Convert Date to Datetime - GeeksforGeeks
July 23, 2025 - We can convert the Date into Datetime in two ways. Using CONVERT() function: Convert means to change the form or value of something. The CONVERT() function in the SQL server is used to convert a value of one type to another type.Convert() 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.
Tutlane
tutlane.com › article › sql-server › convert-format-datetime-in-sql-server-with-examples
Convert (Format) DateTime in SQL Server with Examples - Tutlane
SELECT CONVERT(VARCHAR(30), GETDATE(), 108) ------------------------------------------ 10:12:35 · Like this, we have different format codes available to format the datetime data type value to the required format. The following table lists all the available format codes in SQL Server to convert or format the given datetime value based on our requirements.