HOW to create SQLSERVER functions?

Question

Function 1:

I need to create an sql server function named FirstDayInQtr to return the first day in the respective quarter of year when a date is input. This function should be defined with the following header.

CREATE FUNCTION FirstDayInQtr(@InputDate datetime) RETURNS datetime AS…

Should return the date of first day in the respective quarter. like 1/1/2016

Function 2:

A function to check if an input string consists of UPPERCASE characters. This function should be defined with the following header.

CREATE FUNCTION CheckStringOfUpperAlphaOK(@String varchar(MAX)) RETURNS varchar(6) AS…

Should return "okay" if true and "not okay" if false

Is it a homework day today? What have you done already? What, exactly, are you stuck on? — Rich Benner
– Rich Benner, Commented Jun 24, 2016 at 10:23
run query beginning withCREATE FUNCTION FirstDayInQtr(@InputDate datetime) RETURNS datetime AS… ? — xdd
– xdd, Commented Jun 24, 2016 at 10:23
its an assignment, done with create table and check constraints. stuck with functions and stored procedures — Ayyub
– Ayyub, Commented Jun 24, 2016 at 10:26
your question is not clear. What can't you do? create function or write it's logic? — xdd
– xdd, Commented Jun 24, 2016 at 10:28

Nazir Ullah · Accepted Answer · 2016-06-24 10:49:45Z

2

simply create function in this way

Function 1 for getting first day in quarter

CREATE FUNCTION FirstDayInQtr(@InputDate datetime) 
RETURNS datetime 
AS
BEGIN
DECLARE @day datetime

SELECT @day =  DATEADD(qq, DATEDIFF(qq ,0, @InputDate),0)

Return @day

END

Function2 For checking capital character as

CREATE FUNCTION CheckStringOfUpperAlphaOK(@String varchar(MAX))
Returns VarChar(6)
AS
Begin

    Declare @KeepValues as varchar(50)
    Set @KeepValues = '%[^ ][A-Z]%'
    While PatIndex(@KeepValues collate Latin1_General_Bin, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues collate Latin1_General_Bin, @Temp) + 1, 0, ' ')

    Return @Temp
End

edited Jun 24, 2016 at 10:49

answered Jun 24, 2016 at 10:30

Nazir Ullah

6105 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ayyub Over a year ago

Shows this error . Msg 257, Level 16, State 3, Procedure FirstDayInQtr, Line 9 Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

Arulkumar Over a year ago

@Ayyub: In case if you want the output in this format mm/dd/yyyy, try

DECLARE @day VARCHAR(10); SELECT @day =  CONVERT(VARCHAR(10), DATEADD(qq, DATEDIFF(qq, 0, @InputDate), 0), 101); Return  @day;

Ayyub Over a year ago

@Nazirullah. Date function works perfectly. Jazzakumullahu khairan..... Shows following error for Uppercase function. Must declare the scalar variable "@Temp". The return values for this should be "OKAY" if true and "NOT OKAY" if false.

mvisser · Accepted Answer · 2016-06-24 10:35:13Z

1

This will get you the First Day of the Current Quater. I think thats what you are after

CREATE FUNCTION FirstDayInQtr(@InputDate DATETIME)

RETURNS DATETIME
AS

BEGIN
    DECLARE @firstDayOfCurrentQuater DATETIME
    SELECT @firstDayOfCurrentQuater = DATEADD(qq, DATEDIFF(qq ,0, @InputDate),0)

    RETURN @firstDayOfCurrentQuater;
END

answered Jun 24, 2016 at 10:35

mvisser

6705 silver badges11 bronze badges

Collectives™ on Stack Overflow

HOW to create SQLSERVER functions?

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related