Convert AD pwdLastSet to Datetime

SELECT dateadd(day,90,CONVERT(DATETIME, (132314568622602434 / 864000000000)- 109207 ))  

https://www.sqlservercentral.com/forums/topic/convert-nanoseconds-since-111601  

AD pwdLastSet within 90 days

SELECT dateadd(d,90,Dateadd(hh, Datediff(hh, Getutcdate(), Getdate()), CONVERT(DATETIME, (pwdLastSet / 864000000000)- 109207 ))),* from master.dbo.addata where pwdLastSet > 0  

Get Local Time with GETDATE()

select Dateadd(hh, Datediff(hh, Getutcdate(), Getdate()), CONVERT(DATETIME, (132314568622602434 / 864000000000)- 109207 ))

https://www.got-it.ai/solutions/sqlquerychat/sql-help/data-query/how-to-convert-utc-to-local-time-conversion-in-sql-server/

Mapping a Drive  Drive Map

--https://www.mssqltips.com/sqlservertip/3499/make-network-path-visible-for-sql-server-backup-and-restore-in-ssms/

EXEC XP_CMDSHELL 'net use H: \\chnet\data\ftp_data\Cloverleaf\datatodss\test /user:xDailyCensus 5Qp8sAh4NMMpN'

EXEC XP_CMDSHELL 'Dir H:'

IF OBJECT_ID(N'dbo.tmp', N'U') IS NOT NULL

    DROP TABLE dbo.tmp

CREATE TABLE dbo.tmp (

    unit NVARCHAR(max)

    ,totclosed INT

    )

--Can we name the output file ptflowclsedbed.csv

INSERT INTO dbo.tmp

EXEC CrouseCustom_ClosedBedCounts

DECLARE @sql VARCHAR(8000)

SELECT @sql = 'bcp "select * from dbo.tmp" queryout h:\out.csv -c -t, -T -d APF_OLTP -S' + 'CRH16VTPTFLOWDB' --@@servername

EXEC master..xp_cmdshell @sql

DROP TABLE dbo.tmp

EXEC XP_CMDSHELL 'net use H: /delete'

--S:\FTP_Data\Cloverleaf\datatodss\test

SELECT @@servername

Export out Store Procs output to file from command line

sqlcmd -S crh16vtptflowdb -d APF_OLTP -E -Q "exec dbo.[CrouseCustom_ClosedBedCounts]" -s"," -h-1 -W -o text.csv

Delete trigger to "waste" table example

CREATE TRIGGER trg_ItemDelete ON dbo.Item

AFTER DELETE

AS

INSERT INTO dbo.ItemWaste (

    Name

    ,Amount

    ,TimeWasted

    )

SELECT d.Name

    ,d.Amount

    ,GETDATE()

FROM Deleted d

Update/Insert Trigger example

create trigger triggerAdd on CCR_UserBase

after update,insert

as

begin

   set nocount on;

  /* update tblTriggerTest set OrderApprovalDateTime=getdate()

   from  tblTriggerTest t

   inner join inserted i on t.pkID=i.pkID

   and i.OrderStatus='Approved'*/

     DECLARE @salt UNIQUEIDENTIFIER=NEWID()

         UPDATE dbo.[CCR_UserBase]  SET encPasswdHash= HASHBYTES('SHA2_512', i.Passwd +CAST(@salt AS NVARCHAR(36))),encSalt=@salt,dtLastModified=GETDATE() FROM inserted i LEFT OUTER JOIN dbo.[CCR_UserBase] ub ON ub.[Login]=i.Login

end

Update joining back to original table