Custom Date/Time Formatting in SQL Server

Custom Date/Time formatting in SQL Server

If dt is your datetime column, then

For 1:

SUBSTRING(CONVERT(varchar, dt, 13), 1, 2)
    + UPPER(SUBSTRING(CONVERT(varchar, dt, 13), 4, 3))

For 2:

SUBSTRING(CONVERT(varchar, dt, 100), 13, 2)
    + SUBSTRING(CONVERT(varchar, dt, 100), 16, 3)

Custom date time formatting - SQL server

Try below code. I assumed UTC as your constant from your question description

   SELECT SUBSTRING(DATENAME(DW,GETUTCDATE()),1,3) + ' ' + 
   SUBSTRING(DATENAME(MM,GETUTCDATE()),1,3) +' '+ 
   CAST(DATEPART(DD,GETUTCDATE()) AS VARCHAR(3)) + ' '
   +cast( cast(GETUTCDATE() as time(0)) as varchar(20)) + ' UTC '+ 
   DATENAME(YY,GETUTCDATE())

Convert self-defined/custom datetime format to datetime SQL Server

SQL server have no built-in function which converts custom datetime string into DATETIME format.

SELECT SUBSTRING(literal, 1, 10) + ' ' + REPLACE(SUBSTRING(literal, 12, 8), '-', ':')

fiddle

You may use this expression and create your own function.

How to change datetime format in query?

This is what finally worked:
I Casted the datetime and used FORMAT() with a custom format. The other solutions I tried where a little bit slower.

WHERE s.zeitpunkt
  BETWEEN 
    CAST( 
        FORMAT(CAST($__timeFrom() AS DATETIME),'yyyyMMddHHmmss')
      AS VARCHAR)
    AND CAST(
        FORMAT(CAST($__timeTo() AS DATETIME),'yyyyMMddHHmmss')
      AS VARCHAR)

SQL Custom datetime format to datetime

Via CONVERT?

SELECT CONVERT(DATETIME, '2014-31-01 17:00:00', 103 /* British */);

(No column name)
2014-01-31 17:00:00.000

How to convert date time format in SQL Server 2014

As of SQL Server 2012 the FORMAT function is available allowing you to specify the format of data types and is locale-aware so it will consider date formatting in relation to the session's language or optional culture parameter.

You can achieve your custom formatting like so: FORMAT(GETDATE(), 'd/M/yyyy hh:mm:ss tt')

Note your requested format dd/mm/yyyy hh:mm:ss a is incorrect as in the case of single digits you want to remove zero padding i.e. 10/8/2016 not 10/08/2016. That's why in the format string I use only d and M.

Also, pay attention to @GarethD comment about the cost on larger datasets.

Custom Date format in Sql Server

DATEPART+DATENAME, you can put this into function and use in your query's:

DECLARE @date  datetime = GETDATE()

SELECT  DATENAME(WEEKDAY,@date)+', '+
        DATENAME(MONTH,@date)+' '+
        CAST(DATEPART(DAY,@date) as nvarchar(2))+', '+
        CAST(DATEPART(YEAR,@date) as nvarchar(4))

Output:

Wednesday, September 21, 2016

Converting String field to a custom datetime format in SQL Server

Query

DECLARE @dt VARCHAR(20) = '2015-03-05 01:00'

SELECT REPLACE(CONVERT(VARCHAR(20),CAST(@dt AS DATETIME),106),' ','-')
+' '
+CONVERT(VARCHAR(20),CAST(@dt AS DATETIME),108) AS actDateT;

how to get custom date time format in sql

Problen with varchar(16). The varchar length must be 17 to achieve your requirement.

CONVERT(varchar(17), IFD.dtDateOfIncident, 113)


SELECT REPLACE(CONVERT(varchar(11), IFD.dtDateOfIncident, 106) , ' ', '-') 
       + ' ' + CONVERT(varchar(5), IFD.dtDateOfIncident, 108) 

---

Related Topics