You can use a combination of CONVERT, RIGHT and TRIM to get the desired result:
SELECT ltrim(right(convert(varchar(25), getdate(), 100), 7))
The 100 you see in the function specifies the date format mon dd yyyy hh:miAM (or PM), and from there we just grab the right characters.
You can see more about converting datetimes here.
Answer from LittleBobbyTables - Au Revoir on Stack Overflow Top answer 1 of 7
12
You can use a combination of CONVERT, RIGHT and TRIM to get the desired result:
SELECT ltrim(right(convert(varchar(25), getdate(), 100), 7))
The 100 you see in the function specifies the date format mon dd yyyy hh:miAM (or PM), and from there we just grab the right characters.
You can see more about converting datetimes here.
2 of 7
12
You can use the CONVERT function like this:
SELECT CONVERT(varchar, your_datetime, 108)
However, this is 24-hour clock, no AM/PM.
Microsoft Learn
learn.microsoft.com › en-us › sql › t-sql › functions › format-transact-sql
FORMAT (Transact-SQL) - SQL Server | Microsoft Learn
Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric SQL database in Microsoft Fabric · Returns a value formatted with the specified format and optional culture. Use the FORMAT function for locale-aware formatting of date/time and number values as strings.
Videos
15:30
Display date and time in SQL | Show time in SQL - YouTube
09:05
SQL | How to Convert Date / Time Formats ? | Convert | Format - ...
How to Format Dates in SQL Server using Convert and Format
04:02
Date Time format style In convert() Function with MS-SQL Server ...
30:47
SQL Date & Time Functions (Visually Explained) | FORMAT, CONVERT, ...
00:32
Time Format AM PM SQL Server Query Statement #sqlserver - YouTube
Microsoft Learn
learn.microsoft.com › en-us › sql › t-sql › data-types › time-transact-sql
time (Transact-SQL) - SQL Server | Microsoft Learn
The default string literal format (used for down-level client) will align with the SQL standard form, which is defined as hh:mm:ss[.nnnnnnn]. This format resembles the ISO 8601 definition for TIME excluding fractional seconds. Some down-level clients do not support the time, date, datetime2 and datetimeoffset data types. The following table shows the type mapping between an up-level instance of SQL Server and down-level clients.
MSSQLTips
mssqltips.com › home › sql date format examples using convert function
SQL Date Format Examples using SQL CONVERT Function
September 26, 2025 - SQL Server provides a number of date and time formatting options and in this article we look at how to use SQL CONVERT to output different date/time formats such as mm/dd/yy, mm/dd/yyyy, dd-mm-yyyy, etc.
W3Schools
w3schools.com › sql › sql_dates.asp
Date Functions in SQL Server and MySQL
String Functions: ASCII CHAR_LENGTH ... 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 ...
DataSunrise
datasunrise.com › home › sql server datetime formats: a comprehensive guide
SQL Server Datetime Formats: A Comprehensive Guide
July 17, 2025 - In the following sections, we’ll explore the core data types, formatting options, and best practices for datetime in SQL Server. ... From SQL Server 2016 onward, AT TIME ZONE simplifies converting UTC timestamps into local times (and vice versa) without hard-coding offsets or wrestling with daylight-saving changes.
Microsoft Learn
learn.microsoft.com › en-us › sql › t-sql › functions › date-and-time-data-types-and-functions-transact-sql
Date and Time Data Types and Functions - SQL Server (Transact-SQL) | Microsoft Learn
February 10, 2026 - Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics SQL database in Microsoft Fabric · The sections in this article cover all Transact-SQL date and time data types and functions, including usage and examples.
MSSQLTips
mssqltips.com › home › format sql server dates with format function
Format SQL Server Dates with FORMAT Function
October 31, 2025 - With the SQL Server FORMAT function ... the display format we want and we get that format. Use the FORMAT function to format the date and time data types from a date column (date, datetime, datetime2, smalldatetime, datetimeoffset, ...
Top answer 1 of 3
8
The value of time or datetime data type is not stored with format in sql server. If you want to see the time in a different format you can manipulate the way that time and datetime data types are displayed when converted to a varchar (or nvarchar,nchar,char) data type using some built in functions.
Most often with convert() styles
select convert(char(5),convert(time(0),sysdatetime()))
returns: 22:01
In sql server 2012+ you can use format()
select format(sysutcdatetime(),'HH:mm')
returns: 22:01
But format() can be slower, take a look here: format() is nice and all, but… - Aaron Bertand
2 of 3
0
SELECT LEFT(CAST(getdate() AS Time),5) AS Hours
DotFactory
dofactory.com › sql › time
SQL TIME Data Type
The TIME data type in SQL Server holds time values based on a 24-hour clock with a default format of hh:mm:ss.nnnnnnn.
Database Guide
database.guide › how-to-format-the-date-time-in-sql-server
How to Format the Date & Time in SQL Server
In SQL Server, you can use the T-SQL FORMAT() function to format the date and/or time.
FMS Inc.
fmsinc.com › microsoftaccess › SQLServerUpsizing › AM_PM_date_time_format.htm
Microsoft SQL Server Tip to use AM/PM Non-Military Time Formats in SQL Server
September 6, 2016 - The first expression grabbed the time (5 characters) from the DateTime's 13th character. The second grabs the 2 characters at the end for the AM/PM portion. The nice thing about this approach is that it will work on all versions of SQL Server. Additionally SQL Server 2008 introduced the Datetime2 format to improve upon the Datetime format.
SQL Shack
sqlshack.com › sql-convert-date-functions-and-formats
SQL Convert Date functions and formats
May 21, 2021 - In SQL Server, we have used built-in functions such as SQL GETDATE() and GetUTCDate() to provide server date and format in various formats. SYSDATETIME(): To returns the server’s date and time
W3Schools
w3schools.com › sql › func_sqlserver_convert.asp
SQL Server CONVERT() Function
String Functions: ASCII CHAR_LENGTH ... 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 ...
Simplilearn
simplilearn.com › home › resources › software development › sql date format to convert and format dates easily
SQL Date Format Guide: Syntax & Examples
July 31, 2025 - SQL date variables typically use the yyyy-MM-dd format. For example, DATE, DATETIME, or SMALLDATETIME are the defaults in this format.
LearnSQL.com
learnsql.com › cookbook › how-to-change-date-and-time-formats-in-t-sql
How to Change Date and Time Formats in T-SQL | LearnSQL.com
Transform SQL Server date formats! Learn how to convert dates in SQL Server to a user-friendly format including the day, month, year, and weekday.
C# Corner
c-sharpcorner.com › article › convert-datetime-to-different-formats-in-sql-server
Mastering the Art of Convert DateTime Formats In SQL Server with Over 30 Code Examples
October 4, 2023 - Learn how to convert datetime values to different formats in SQL Server with this comprehensive guide. Explore various date conversion functions and formats, including examples and code snippets, to efficiently manipulate and format date and time data in your SQL Server queries.
SQLServerCentral
sqlservercentral.com › articles › how-to-format-dates-in-sql-server-hint-dont-use-format
How to Format Dates in SQL Server (Hint: Don’t Use FORMAT!) – SQLServerCentral
August 5, 2022 - SQL Server is expensive compared to the application layer and shouldn’t usually be bogged down with formatting tasks. If done properly in the “Presentation Layer”, the dates and times can follow the “local formatting” that the user has on their computer.
Microsoft Learn
learn.microsoft.com › en-us › sql › t-sql › data-types › datetime-transact-sql
datetime (Transact-SQL) - SQL Server | Microsoft Learn
For example, 5/20/97 represents the twentieth day of May 1997. When you use numeric date format, specify the month, day, and year in a string that uses slash marks (/), hyphens (-), or periods (.) as separators. This string must appear in the following form: <number separator number separator number [time] [time]>
Count
count.co › sql-resources › sql-server › dates-and-times
Dates and Times | SQL Server - Count.co
In order to convert your string to one of the date types, your string must be in one of the following formats: DATE: YYYY-MM-DD, although t-sql supports the formats myd, mdy, dmy, dym, ymd.