TABLE vALUED FUNCTION

 USE [SystemTracker]

GO

/****** Object:  UserDefinedFunction [dbo].[FN_LAST_WORKINGWEEK]    Script Date: 17-11-2021 15:35:08 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*---------------------------------------------------------------------------------------------------------

FUNCTION NAME       : FN_LAST_WORKINGWEEK

FUNCTION / PURPOSE : FUNCTION CALCULATES THE LAST WORKING WEEK OF THE USER

AUTHOR NAME : PANKAJ SAINI(2470)

CREATED DATE : 06-NOV-2017

REVISION HISTORY 1 : BY: PANKAJ SAINI -- ON: 06-NOV-2017 -- REVISION DETAILS: NEW FUNCTION CREATED.

-----------------------------------------------------------------------------------------------------------*/

ALTER FUNCTION [dbo].[FN_LAST_WORKINGWEEK]()

RETURNS @OUTPUTTBL TABLE (DATEVALSTART DATE, DATEVALEND DATE)

BEGIN

---------------------------------------------------------------------------------------------

-- DEFINED PARAMETER(S)    | VARIABLE DATA TYPE | DEFAULT VAL -- COMMENTS

---------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------


 INSERT INTO @OUTPUTTBL (DATEVALSTART, DATEVALEND)

 VALUES (CONVERT(DATE,DATEADD(dd, ((DATEDIFF(dd, '17530101', GETDATE()) / 7) * 7) - 7, '17530101')), --GIVES THE DATE OF MONDAY

 CONVERT(DATE,DATEADD(dd, ((DATEDIFF(dd, '17530101', GETDATE()) / 7) * 7) - 1, '17530101'))); -- GIVES THE DATE OF SUNDAY

 RETURN 


END

Comments

Post a Comment

Popular posts from this blog

Npgsql query and format with output parameters

  public string UploadExceldata(string json)         {             string connectionString = String.Format(new Connection().ConnectionString);             DataSet ds = new DataSet();             BaseResultData oBaseResult = new BaseResultData();             var connection = new Npgsql.NpgsqlConnection(connectionString);             try             {                                if (!(connection.State == ConnectionState.Open))                 {                     connection.Open();                 }                 //NpgsqlTransaction tra...
Read more

Return DataSet from Npgsql with multiple result set as Tables

clsPSQLConnectionCreator dbHelper = new clsPSQLConnectionCreator();         NpgsqlCommand sc = dbHelper.GetSqlQueryCommand("select * from systemtracker_dbo.pr_get_dataset('parameter1','parameter2','ref_cursor1','ref_cursor1','ref_cursor1','ref_cursor1','ref_cursor1','ref_cursor1','ref_cursor1','ref_cursor1');FETCH ALL IN t1; FETCH ALL IN t2;FETCH ALL IN t3;FETCH ALL IN t4;FETCH ALL IN t5;FETCH ALL IN t6;FETCH ALL IN t7;FETCH ALL IN t8;");         DataSet ds = dbHelper.LoadDataSet(sc);         return ds; GetSqlQueryCommand function in helper class which consist connection as well      public NpgsqlCommand GetSqlQueryCommand (string query)     {         return PrepareCommand(CommandType.Text, query);     }  private NpgsqlCommand PrepareCommand (CommandType commandType, string commandText)     {         if (connection == n...
Read more

Npgsql Helper class

 using System; using System.Collections.Generic; using System.Text; using System.Data; using Npgsql; using NpgsqlTypes; namespace AI_Portal_Cloud.DAL {     public class clsPSQLHelper : IDisposable     {         private NpgsqlConnection connection = null;         private String connectionString = "dbConnection";         private NpgsqlDataAdapter sqlData = new NpgsqlDataAdapter();         public NpgsqlCommand sqlCmd = new NpgsqlCommand();                  // {0} : IP, {1} : DB Name, {2} : userID, {3} : userPW         private String connectionFormat = "Server={0};Database={1};User Id={2};Password={3};Port={4}";         public String ConnectionString         {             set             {           ...
Read more