Mod' Is Not a Recognized Built-In Function Name

MOD' is not a recognized built-in function name

The MOD keyword only exists in the DAX language (tabular dimensional queries), not TSQL

Use % instead.

Ref: Modulo

is not a recognized built-in function name

Add [dbo] in prefix and then execute as same :

SELECT [dbo].[Split_On_Upper_Case] ('SaiBharath')

SQL Server 2014, 'format' is not a recognized built-in function name

Technically, Gabriele's answer is correct - He is explaining why you're getting the error message you're getting.

However, there is a better way to get your results, that doesn't involve Format.

First thing's first: You should seriously consider converting the [Date_Time] column you have now from (What I guess based on the code in the question is) a string to a proper DateTime.

That will solve not only this specific problem but a bunch of many other problems, some of them you probably have encountered before.

Even if this involves a lot of work it will be worth it in the long run, trust me.

However, considering that's not an option, I would suggest a couple of improvements to your existing code:

1. Use cast to convert the first 8 chars directly to a date, instead of converting the entire thing to a bigint and then using format and implicit conversion like your code is attempting to do now.

2. Use WeekDay instead of dw in the DatePart function. It's just more readable.

3. Calculate the week day number based on the @@DateFirst value - because SQL Server does exactly that, meaning if someone changes the date first settings your current code will produce the wrong results.

Having said all that, let's see how that looks like in code.

First, create and populate sample table (Please save us this step in your future questions):

CREATE TABLE SCORE_DATA (
  [Name] varchar(1), 
  [Score] int, 
  [Date_time] char(14)
);
    
INSERT INTO SCORE_DATA ([Name], [Score], [Date_time]) VALUES
('A', 100, '20200601000000'),
('B', 120, '20200615000000'),
('C', 110, '20200629000000'),
('B', 150, '20200701000000');

The query (with explanatory comments):

SELECT  [Name]
      , [Score]
FROM SCORE_DATA
      -- using full name for readability
WHERE (DATEPART(WeekDay, 
                 -- ISO8601 format is unambiguous and will always be converted correctly
                 CAST(
                   -- A string representation of date in ISO8601 Format: yyyyMMdd
                   LEFT([Date_Time], 8) 
                 AS Date) -- You don't need DateTime, just the date...
        -- taking datefirst settings into consideration to get a fixed number
        ) + @@DATEFIRST) % 7 = 2

Results:

Name    Score
A       100
B       120
C       110 -- June 29th 2020 was a Monday too...

MID' is not a recognized built-in function name

use SUBSTR OR SUBSTRING I forgot it. mid is function properly if java is enabled in ASE.

Happy coding

TRIM' is not a recognized built-in function name

You need to use the Schema prefix when calling user defined functions. In your case this will most likely be "dbo".

Change your select statement to:

declare @s varchar(25)
set @s = '      Amru    '
select dbo.TRIM(@s)

Built-in function Name error while joining two tables?

Adding (MONTH) after Mem_Date makes SQL Server think it's a function that you are trying to pass a parameter to. Correct it to:

select 
    e.Meme_lev2 as Group#, 
    e.MEME_GRPN as Groupname, 
    MONTH(x.mem_date) AS mem_date, -- changed
    COUNT(*)
from Impact.dbo.tbl_mem m
left join Impact.dbo.tbl_meme e 
    on e.MEME_ID1 = m.MEM_ID1
inner join impactwork.dbo.tbl_mmonth x 
    on x.mem_date between convert(varchar(10), e.meme_eff, 101) and convert(varchar(10), e.meme_trm, 101)
    and x.mem_date between '01/01/2015' and '03/31/2015'
where
    e.MEME_LEV2 = '52024' 
    and GETDATE() between '01/16/2015' and '06/16/2015'
group by 
    e.Meme_lev2, 
    e.MEME_GRPN, 
    MONTH(x.mem_date) 

Please note that I also added the additional fields in your SELECT statement to the GROUP BY clause.

EDIT: so you can see what @jarlh is suggesting:

select 
    e.Meme_lev2 as Group#, 
    e.MEME_GRPN as Groupname, 
    MONTH(x.mem_date) AS mem_date, -- changed
    COUNT(*)
from 
    Impact.dbo.tbl_mem m
     left join 
    Impact.dbo.tbl_meme e on 
        e.MEME_ID1 = m.MEM_ID1 AND 
        e.MEME_LEV2 = '52024' 
     inner join impactwork.dbo.tbl_mmonth x on 
            x.mem_date between convert(varchar(10), e.meme_eff, 101) and convert(varchar(10), e.meme_trm, 101) and 
            x.mem_date between '01/01/2015' and '03/31/2015'
--where
--  GETDATE() between '01/16/2015' and '06/16/2015'
group by 
    e.Meme_lev2, 
    e.MEME_GRPN, 
    MONTH(x.mem_date) 

---

Related Topics