Custom Date/Time formatting in SQL Server

Question

I am trying to write a stored procedure which selects columns from a table and adds 2 extra columns to the ResultSet. These 2 extra columns are the result of conversions on a field in the table which is a Datetime field.

The Datetime format field has the following format 'YYYY-MM-DD HH:MM:SS.S'

The 2 additional fields which should be in the following format:

1. DDMMM
2. HHMMT, where T is 'A' for a.m. and 'P' for p.m.

Example: If the data in the field was '2008-10-12 13:19:12.0' then the extracted fields should contain:

1. 12OCT
2. 0119P

I have tried using CONVERT string formats, but none of the formats match the output I want to get. I am thinking along the lines of extracting the field data via CONVERT and then using REPLACE, but I surely need some help here, as I am no sure.

Could anyone well versed in stored procedures help me out here? Thanks!

Answer 1

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)

Answer 2

Use DATENAME and wrap the logic in a Function, not a Stored Proc

declare @myTime as DateTime

set @myTime = GETDATE()

select @myTime

select DATENAME(day, @myTime) + SUBSTRING(UPPER(DATENAME(month, @myTime)), 0,4)

Returns "14OCT"

Try not to use any Character / String based operations if possible when working with dates. They are numerical (a float) and performance will suffer from those data type conversions.

Dig these handy conversions I have compiled over the years...

/* Common date functions */
--//This contains common date functions for MSSQL server

/*Getting Parts of a DateTime*/
    --//gets the date only, 20x faster than using Convert/Cast to varchar
    --//this has been especially useful for JOINS
    SELECT (CAST(FLOOR(CAST(GETDATE() as FLOAT)) AS DateTime))

    --//gets the time only (date portion is '1900-01-01' and is considered the "0 time" of dates in MSSQL, even with the datatype min value of 01/01/1753. 
    SELECT (GETDATE() - (CAST(FLOOR(CAST(GETDATE() as FLOAT)) AS DateTime)))


/*Relative Dates*/
--//These are all functions that will calculate a date relative to the current date and time
    /*Current Day*/
    --//now
    SELECT (GETDATE())

    --//midnight of today
    SELECT (DATEADD(ms,-4,(DATEADD(dd,DATEDIFF(dd,0,GETDATE()) + 1,0))))

    --//Current Hour
    SELECT DATEADD(hh,DATEPART(hh,GETDATE()),CAST(FLOOR(CAST(GETDATE() AS FLOAT)) as DateTime))

    --//Current Half-Hour - if its 9:36, this will show 9:30
    SELECT DATEADD(mi,((DATEDIFF(mi,(CAST(FLOOR(CAST(GETDATE() as FLOAT)) as DateTime)), GETDATE())) / 30) * 30,(CAST(FLOOR(CAST(GETDATE() as FLOAT)) as DateTime)))

    /*Yearly*/
    --//first datetime of the current year
    SELECT (DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))

    --//last datetime of the current year
    SELECT (DATEADD(ms,-4,(DATEADD(yy,DATEDIFF(yy,0,GETDATE()) + 1,0))))

    /*Monthly*/
    --//first datetime of current month
    SELECT (DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))

    --//last datetime of the current month
    SELECT (DATEADD(ms,-4,DATEADD(mm,1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))))

    --//first datetime of the previous month
    SELECT (DATEADD(mm,DATEDIFF(mm,0,GETDATE()) -1,0))

    --//last datetime of the previous month
    SELECT (DATEADD(ms, -4,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)))

    /*Weekly*/
    --//previous monday at 12AM
    SELECT (DATEADD(wk,DATEDIFF(wk,0,GETDATE()) -1 ,0))

    --//previous friday at 11:59:59 PM
    SELECT (DATEADD(ms,-4,DATEADD(dd,5,DATEADD(wk,DATEDIFF(wk,0,GETDATE()) -1 ,0))))

    /*Quarterly*/
    --//first datetime of current quarter
    SELECT (DATEADD(qq,DATEDIFF(qq,0,GETDATE()),0))

    --//last datetime of current quarter
    SELECT (DATEADD(ms,-4,DATEADD(qq,DATEDIFF(qq,0,GETDATE()) + 1,0)))

Answer 3

You can use the following command in SQL server to make it:

select FORMAT(getdate(), N'yyyy-MM-ddThh:mm:ss')

Answer 4

Not answering your question specifically, but isn't that something that should be handled by the presentation layer of your application. Doing it the way you describe creates extra processing on the database end as well as adding extra network traffic (assuming the database exists on a different machine than the application), for something that could be easily computed on the application side, with more rich date processing libraries, as well as being more language agnostic, especially in the case of your first example which contains the abbreviated month name. Anyway the answers others give you should point you in the right direction if you still decide to go this route.

Answer 5

The Datetime format field has the following format 'YYYY-MM-DD HH:MM:SS.S'

That statement is false. That's just how Enterprise Manager or SQL Server chooses to show the date. Internally it's a 8-byte binary value, which is why some of the functions posted by Andrew will work so well.

Kibbee makes a valid point as well, and in a perfect world I would agree with him. However, sometimes you want to bind query results directly to display control or widgets and there's really not a chance to do any formatting. And sometimes the presentation layer lives on a web server that's even busier than the database. With those in mind, it's not necessarily a bad thing to know how to do this in SQL.

Answer 6

Yes Depart is a solution for that but I think this kind of methods are long trips!

SQL SERVER:

SELECT CAST(DATEPART(DD,GETDATE()) AS VARCHAR)+'/'
+CAST(DATEPART(MM,GETDATE()) AS VARCHAR)
+'/'+CAST(DATEPART(YYYY,GETDATE()) AS VARCHAR)
+' '+CAST(DATEPART(HH,GETDATE()) AS VARCHAR)
+':'+CAST(DATEPART(MI,GETDATE()) AS VARCHAR)

Oracle:

Select to_char(sysdate,'DD/MM/YYYY HH24:MI') from dual

You may write your own function by this way you can get rid of this mess;

http://sql.dzone.com/news/custom-date-formatting-sql-ser

select myshortfun(getdate(),myformat)
GO

Answer 7

You're going to need DATEPART here. You can concatenate the results of the DATEPART calls together.

To get the month abbreviations, you might be able to use DATENAME; if that doesn't work for you, you can use a CASE statement on the DATEPART.

DATEPART also works for the time field.

I can think of a couple of ways of getting the AM/PM indicator, including comparing new dates built via DATEPART or calculating the total seconds elapsed in the day and comparing that to known AM/PM thresholds.

Answer 8

in MS SQL Server you can do:

SET DATEFORMAT ymd

year, month, day,

Answer 9

If it's something more specific like DateKey (yyyymmdd) that you need for dimensional models, I suggest something without any casts/converts:

DECLARE @DateKeyToday int = (SELECT 10000 * DATEPART(yy,GETDATE()) + 100 * DATEPART(mm,GETDATE()) + DATEPART(dd,GETDATE()));
PRINT @DateKeyToday

Answer 10

I'm adding this answer (for myself) as relevant to custom formatting.

For underscore yyyy_MM_dd

REPLACE(SUBSTRING(CONVERT(VARCHAR, @dt, 120), 1, 10),'-','_')