Format Function doesnt work

Transact-SQL
,
1

Hi,

Just run the script below in SQL 2008 R2 and I'm getting an error "Format is not recognized'. I'm thinking the SQL server installed is not compatible or below 2012 version.

Any idea or sample to convert this script to run 2008 R2 .
Find below a sample DDL and desired result. Thank you in advance.

declare @sample table (dt datetime)

insert @sample values ('2017-10-09 20:04:00.000')
insert @sample values ('2017-10-07 00:55:22.000')
insert @sample values ('2017-10-16 15:52:34.000')
insert @sample values ('2017-10-06 03:09:31.000')
insert @sample values ('2017-10-06 19:56:09.000')
insert @sample values ('2017-10-06 06:36:39.000')
insert @sample values ('2017-10-05 16:19:14.000')

select 
    s.dt,
    DATEADD(hh,8,s.dt) AS dt_plus_8,
    FORMAT(CAST(DATEADD(HH,8,s.dt) AS DATETIME), 'hh tt') as TIME_HOURLY
from
    @sample s


Desired result:
DT----------------------DT_Plus_8--------------Time_Hourly
----------------------------------------------------------
2017-10-09 20:04:00.000--2017-10-10 04:00:00.000--04 AM
2017-10-07 00:55:22.000--2017-10-07 08:00:00.000--08 AM
2017-10-16 15:52:34.000--2017-10-16 23:00:00.000--11 PM
2017-10-06 03:09:31.000--2017-10-06 11:00:00.000--11 AM
2017-10-06 19:56:09.000--2017-10-07 03:00:00.000--03 AM
2017-10-06 06:36:39.000--2017-10-06 14:00:00.000--02 PM
2017-10-05 16:19:14.000--2017-10-06 00:00:00.000--12 AM
2

FORMAT is new starting with sql server 2012

3

How about for 2008 R2. Is there any idea to convert this code FORMAT(CAST(DATEADD(HH,8,s.dt) AS DATETIME), 'hh tt') as TIME_HOURLY

4

This is one way ; definitely there are a lot of ways for it

declare @dt as datetime =
	    '2017-10-09 20:04:00.000'
		--'2017-10-16 15:52:34.000'


select 
	RIGHT('00' + CONVERT(varchar(2), DATEPART(HH,DATEADD(HH,8,@dt))%12) ,2)
	+' ' + CASE WHEN DATEPART(HH, DATEADD(HH,8,@dt))  < 12 THEN 'AM' ELSE 'PM' END


select
	RIGHT('00' + CONVERT(varchar(2), DATEPART(HH,DATEADD(HH,8,@dt))%12) ,2)
	+ ' ' + RiGHT(CONVERT(varchar(20),DATEADD(HH,8,@dt),100),2)
5

Thank you very much stepson :grinning:

6

You're welcome!

7 
declare @dt datetime
set @dt = getdate()
select stuff(replace(right(convert(varchar(30), @dt, 0), 7), ' ', '0'), 3, 3, ' ')