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!

Sherrylsherurd

These output results you are looking for remind me of the dates on the bottom of soup cans. You only need a sp if you are going to do this repeatedly. – Schaub

You are correct, I am going to do this repeatedly. – Sherrylsherurd

Please refer to "SQL Server", not "SQL", because different databases have different way to format dates. – Hyracoid

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)

Forbore

Thanks for your answer. Helped me a lot! I used format 106, instead of 13 for the 1st part. Is it possible to do a left hand side zero padding for format 2 (time), if the time reported is something like 924P. – Sherrylsherurd

It's definitely possible, but how to pad with zeroes in SQL Server is a different question. Here's one example: #16761400 – Matriculate

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)))

Schaub

Thanks for your pointers and handy function reference. It should come in handy! – Sherrylsherurd

Answer 3

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

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

Stiff

This answer is only valid for >=2014 versions – Aiden

The function is valid too for SQL Server version 2012 SP4 – Paphlagonia

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.

Authority

Yes, you are absolutely right and I echo your sentiments here, but there are people above me who want this thing to be handled in the sp. Otherwise, I probably wouldn't be asking this question. :) – Sherrylsherurd

Sometimes you want to enforce a standard at your database boundary, and sometimes you want to combine columns into shorter output columns - and it is more efficient to have SQL Server do that before it sends it down the wire. – Forbore

That being said, if you must do this, wrap the formatting code in a function of it's own so the format can be duplicated easily in whichever queries/stored procedures you need to use it in. – Authority

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.

Binnacle

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

Gravure

If you're going down the datepart road, you may also need leading zeros on the day: RIGHT('00' + CONVERT(NVARCHAR(2), DATEPART(DAY, GETDATE())), 2), – Gallup

Shame SQL Server doesn't have this same function. Just look at that stupid syntax! – Kare

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.

Libb

Answer 8

in MS SQL Server you can do:

SET DATEFORMAT ymd

year, month, day,

Bite

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

Berl

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),'-','_')

Nancynandor