SELECT Fridays = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), n.num)
FROM (SELECT TOP 366 num = ROW_NUMBER() OVER(ORDER BY a.NAME)-1 FROM dbo.syscolumns a, dbo.syscolumns b) n
WHERE DATENAME(weekday, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), n.num)) = 'Friday'
epoch date is generally in seconds and milliseconds.
sample: /Date(1665424800000)/
javascript code to convert in readable date:
var lsad = String(LoanSanctionApproveDate);
var lsadUnixDate = lsad.slice(6, 19);
var lsadToInt = parseInt(lsadUnixDate);
var loanSancAprvDate = new Date(lsadToInt).toLocaleDateString('es-CL');
Insert into Table From stored procedure execution
Insert INTo PFStatement_Data
EXEC GCPF.SP_RPT_GetProvidentStatement '29226', '31 December, 2021', 1
C# Code To access API using basic authorization.
using (HttpClient client = new HttpClient())
{
string TransactionID = "6334360607";
string url = $"http://103.11.137.17:8081/BillPayGW/BillInquiryService?shortcode=555&userid=dbbl&password=dbbl&txnid={ TransactionID }";
Uri baseUri = new Uri(url);
//This is the key section UserName Password
var plainTextBytes = System.Text.Encoding.UTF8.GetBytes("dbill:dBILL!23");
string base64EncodedAuthenticationString = System.Convert.ToBase64String(plainTextBytes);
var requestMessage = new HttpRequestMessage(HttpMethod.Get, baseUri);
requestMessage.Headers.Authorization = new AuthenticationHeaderValue("Basic", base64EncodedAuthenticationString);
requestMessage.Headers.Add("responseType", "json");
//make the request
var task = client.SendAsync(requestMessage);
var response = task.Result;
response.EnsureSuccessStatusCode();
string responseBody = response.Content.ReadAsStringAsync().Result;
}
Postman setting:
--SELECT ETinNo FROM Employee
IF NOT EXISTS (
SELECT *
FROM sys.columns
WHERE object_id = OBJECT_ID(N'[dbo].[Employee]')
AND name = 'ETinNo'
)
BEGIN
ALTER TABLE Employee
ADD ETinNo varchar(20);
SELECT 1
END
SET NOCOUNT ON
-- Table variable to hold InputBuffer data
DECLARE @Inputbuffer TABLE
(
EventType NVARCHAR(30) NULL,
Parameters INT NULL,
EventInfo NVARCHAR(4000) NULL
)
-- Table variable to hold running processes information
DECLARE @BusyProcess TABLE
(
SPID INT,
Status VARCHAR(100),
Login VARCHAR(100),
HostName VARCHAR(100),
DBName VARCHAR(100),
Command VARCHAR(200),
CPUTime INT,
DiskIO INT,
LastBatch DATETIME,
ProgramName VARCHAR(200),
EventInfo NVARCHAR(4000), -- extra column to hold actual stored procedure or batch call text
EventTime INT -- time in minutes, a process is running
)
-- Insert all running processes information to table variable
INSERT @BusyProcess
( SPID, Status, Login, HostName, DBName, Command, CPUTime,
DiskIO, LastBatch, ProgramName )
SELECT spid,status,loginame,hostname,DB_NAME(dbid),cmd,cpu,physical_io,last_batch,program_name
FROM SYS.SYSPROCESSES
WHERE
1 = CASE WHEN Status IN ( 'RUNNABLE', 'SUSPENDED' ) THEN 1
--Transactions that are open not yet committed or rolledback
WHEN Status = 'SLEEPING' AND open_tran > 0 THEN 1
ELSE 0 END
AND cmd NOT LIKE 'BACKUP%' and DB_NAME(dbid)='gHRM'
-- Cursor to add actuall Procedure or Batch statement for each process
DECLARE cur_BusyProcess Cursor
FOR SELECT SPID
FROM @BusyProcess
OPEN cur_BusyProcess
DECLARE @SPID INT
Fetch NEXT FROM cur_BusyProcess INTO @SPID
While ( @@FETCH_STATUS <> -1 )
BEGIN
INSERT @Inputbuffer
EXEC ( 'DBCC INPUTBUFFER(' + @SPID + ')'
)
UPDATE @BusyProcess
SET EventInfo = I.EventInfo,
EventTime = DATEDIFF(MI,LastBatch,GETDATE())
FROM @BusyProcess b
CROSS JOIN @Inputbuffer i
WHERE B.SPID = @SPID
DELETE FROM @Inputbuffer
FETCH NEXT FROM cur_BusyProcess INTO @SPID
END
CLOSE cur_BusyProcess
DEALLOCATE cur_BusyProcess
-- Create html mail
IF EXISTS(SELECT 1
FROM @BusyProcess I
WHERE EventInfo NOT LIKE '---BusyProcess Detection%'
AND EventTime >= 3
)
BEGIN
Declare @Body varchar(max), @TableHead varchar(1000), @TableTail varchar(1000)
Set NoCount On;
DECLARE @QKILLsp VARCHAR(1000)
SET @QKILLsp= (SELECT DISTINCT ' KILL '+ CONVERT(VARCHAR,SPID)
FROM @BusyProcess I
WHERE EventInfo NOT LIKE '---BusyProcess Detection%'
AND EventTime >= 3 -- Transactions Running for 3 minutes or more
for XML path('')
)
EXEC(@QKILLsp)
END
SELECT OfficeId, JoiningDate, ReleaseDate FROM trns.EmployeeTransfer WHERE EmployeeId = 521
Order By JoiningDate
------------------------------------------------------------------------------------------------------------------
SELECT OfficeId, JoiningDate, ReleaseDate ,
isNUll(
LEAD(ISNULL(OfficeId,0)) OVER (ORDER BY JoiningDate)
, isNull(OfficeId, 0)
) AS NextOfficeId
FROM trns.EmployeeTransfer
WHERE EmployeeId = 521
