Format Function Not Working in SQL Server 2008 R2

FORMAT function not working in sql server 2008 R2

FORMAT function is available from version 2012 onwards. In earlier versions, use this:

DECLARE @d DATETIME = '01/01/2011'; 
SELECT replace(replace(' '+convert(varchar(10),@d,101),' 0',''),'/0','/')

However, formatting is the job of the front end application.

SQL Format('MMMM') does not work in SQL Server 2008

This is a feature with SQL Server 2012 and higher. Try DATENAME(MONTH,[time]).

Format() function doesn't work?

Use Convert function instead. Example:

select convert(varchar(5), GETDATE(), 126) + convert(varchar(5), GETDATE(), 105)

Alternative to using FORMAT in SQL Server 2008 R2

You can achieve this far more easily by just using CONVERT:

SELECT CONVERT(varchar(19),GETDATE(),127) + 'Z';

As I mentioned in my comment FORMAT is actually an awful function, it performs terribly. I posted an answer to another question earlier today on just how badly it does compared to a CONVERT. Don't just use this expression on your 2008- servers, replace the FORMAT expression on your 2012+ servers with this one too.

How to format date on MS SQL Server 2008

I had a similar problem in SQL 2012 they have a great function called format which you could pass in a date and the format you require

SELECT FORMAT(GETDATE(), 'dd/mm/yyy');

put there is nothing similar in SQL Server 2008. I created a simple function to do a similar job, it might need tweaking but is not a bad base.

-- =============================================
-- Author:      Luke Mc Redmond
-- =============================================
CREATE FUNCTION [dbo].[FormatDate]
(
-- Add the parameters for the function here
@Date   DATETIME,
@Format NVARCHAR(50)
)
RETURNS NVARCHAR(50)
AS
 BEGIN

   DECLARE @ResultVar nvarchar(50) = UPPER(@Format);

   DECLARE @d nvarchar(20) = CONVERT(nvarchar(20), DATEPART(day, @Date));
   DECLARE @dd nvarchar(20) = CONVERT(nvarchar(20), DATEPART(day, @Date));
   DECLARE @day nvarchar(20) = DATENAME(weekday, @Date);
   DECLARE @ddd nvarchar(20) = DATENAME(weekday, @Date)+' '+CONVERT(nvarchar(5), DATEPART(day, @Date));

   DECLARE @m nvarchar(20) = CONVERT(nvarchar, DATEPART(month, @Date));
   DECLARE @mm nvarchar(20) = CONVERT(nvarchar, DATEPART(month, @Date));
   DECLARE @mmm nvarchar(20) = CONVERT(VARCHAR(3), DATENAME(month, @Date), 100);
   DECLARE @month nvarchar(20) = DATENAME(month, @Date);

   DECLARE @y nvarchar(20) = CONVERT(nvarchar, DATEPART(year, @Date));
   DECLARE @yy nvarchar(20) = RIGHT(CONVERT(nvarchar, DATEPART(year, GETDATE())),2);
   DECLARE @yyy nvarchar(20) = CONVERT(nvarchar, DATEPART(year, @Date));
   DECLARE @yyyy nvarchar(20) = CONVERT(nvarchar, DATEPART(year, @Date));
   DECLARE @year nvarchar(20) = CONVERT(nvarchar, DATEPART(year, @Date));

   SELECT @ResultVar = CASE WHEN CHARINDEX('DAY',@ResultVar) > 0 THEN  REPLACE(@ResultVar collate Latin1_General_CS_AS ,'DAY' collate Latin1_General_CS_AS ,@day)
                        WHEN CHARINDEX('DDD',@ResultVar) > 0 THEN REPLACE(@ResultVar collate Latin1_General_CS_AS ,'DDD' collate Latin1_General_CS_AS ,@ddd)
                       WHEN CHARINDEX('DD',@ResultVar) > 0 THEN REPLACE(@ResultVar collate Latin1_General_CS_AS ,'DD' collate Latin1_General_CS_AS ,@dd)
                       WHEN CHARINDEX('D',@ResultVar) > 0 THEN REPLACE(@ResultVar collate Latin1_General_CS_AS,'D' collate Latin1_General_CS_AS ,@d) END;

   SELECT @ResultVar = REPLACE(@ResultVar collate Latin1_General_CS_AS,'Monday' collate Latin1_General_CS_AS ,'monday')

   SELECT @ResultVar = CASE WHEN CHARINDEX('MONTH',@ResultVar) > 0 THEN  REPLACE(@ResultVar collate Latin1_General_CS_AS,'MONTH' collate Latin1_General_CS_AS,@month)
                       WHEN CHARINDEX('MMM',@ResultVar) > 0 THEN REPLACE(@ResultVar collate Latin1_General_CS_AS ,'MMM' collate Latin1_General_CS_AS,@mmm)
                       WHEN CHARINDEX('MM',@ResultVar) > 0 THEN REPLACE(@ResultVar collate Latin1_General_CS_AS,'MM' collate Latin1_General_CS_AS,@mm)
                       WHEN CHARINDEX('M',@ResultVar) > 0 THEN REPLACE(@ResultVar collate Latin1_General_CS_AS,'M' collate Latin1_General_CS_AS,@m) END;

   SELECT @ResultVar = CASE WHEN CHARINDEX('YEAR',@ResultVar) > 0 THEN  REPLACE(@ResultVar collate Latin1_General_CS_AS,'YEAR' collate Latin1_General_CS_AS ,@year)
                       WHEN CHARINDEX('YYYY',@ResultVar) > 0 THEN REPLACE(@ResultVar collate Latin1_General_CS_AS,'YYYY' collate Latin1_General_CS_AS ,@yyyy)
                       WHEN CHARINDEX('YYY',@ResultVar) > 0 THEN REPLACE(@ResultVar collate Latin1_General_CS_AS ,'YYY' collate Latin1_General_CS_AS ,@yyy)
                       WHEN CHARINDEX('YY',@ResultVar) > 0 THEN REPLACE(@ResultVar collate Latin1_General_CS_AS ,'YY' collate Latin1_General_CS_AS ,@yy)
                       WHEN CHARINDEX('Y',@ResultVar) > 0 THEN REPLACE(@ResultVar collate Latin1_General_CS_AS,'Y' collate Latin1_General_CS_AS ,@y) END;

   SELECT @ResultVar = REPLACE(@ResultVar collate Latin1_General_CS_AS,'monday' collate Latin1_General_CS_AS ,'Monday')

   RETURN @ResultVar

 END;
 GO

Hope this helps!

How do I format a number in T-SQL for SQL Server 2008 R2?

Considering only 0-9 needs 0 to be appended before.

Declare @num int =5

SELECT CASE 
         WHEN Len(@num) = 1 THEN '0' + Cast(@num AS VARCHAR(10)) 
         ELSE Cast(@num AS VARCHAR(10)) 
       END 

SQL Server 2008: how to format the output as a currency

If you are looking for a "true" Currency format, similar to what can be achieved via the FORMAT function that started in SQL Server 2012, then you can achieve the exact same functionality via SQLCLR. You can either code the simple .ToString("C" [, optional culture info]) yourself, or you can download the SQL# library (which I wrote, but this function is in the Free version) and use it just like the T-SQL FORMAT function.

For example:

SELECT SQL#.Math_FormatDecimal(123.456, N'C', N'en-us');

Output:

$123.46

SELECT SQL#.Math_FormatDecimal(123.456, N'C', N'fr-fr');

Output:

123,46 €

This approach works in SQL Server 2005 / 2008 / 2008 R2. And, if / when you do upgrade to a newer version of SQL Server, you have the option of easily switching to the native T-SQL function by doing nothing more than changing the name SQL#.Math_FormatDecimal to be just FORMAT.

Putting this into the context of the query from the original question:

SELECT SQL#.Math_FormatDecimal(COALESCE(SUM(SUBTOTAL),0), N'C', N'en-us') AS [Total]
FROM dbo.SALESORD_HDR 
where ORDERDATE = datediff(d,0,getdate()) 
and STATUS NOT IN (3,6)

EDIT:

OR, since it seems that only en-us format is desired, there is a short-cut that is just too easy: Converting from either the MONEY or SMALLMONEY datatypes using the CONVERT function has a "style" for en-us minus the currency symbol, but that is easy enough to add:

SELECT '$' + CONVERT(VARCHAR(50),
                CONVERT(MONEY, COALESCE(SUM(SUBTOTAL), 0)),
                1) AS [Total]
FROM dbo.SALESORD_HDR 
where ORDERDATE = datediff(d,0,getdate()) 
and STATUS NOT IN (3,6)

Since the source datatype of the SUBTOTAL field is FLOAT, it first needs to be converted to MONEY and then converted to VARCHAR. But, the optional "style" is one reason I prefer CONVERT over CAST.

---

Related Topics