Date Functions in SQL

-- SQL Server current system date and conversions -- getdate() function
SELECT Now=GETDATE() -- 2012-01-02 15:13:38.607

SELECT CONVERT(datetime, getdate()) -- 2012-01-02 15:13:38.607

SELECT CONVERT(datetime2, getdate()) -- 2012-01-02 15:13:38.6070000

SELECT CONVERT(smalldatetime, getdate()) -- 2012-01-02 15:14:00

SELECT CONVERT(date, getdate()) -- 2012-01-02

SELECT CONVERT(datetime, CURRENT_TIMESTAMP) -- 2012-01-02 15:13:38.607

-- SQL Server current system date functions
SELECT SYSDATETIME() -- 2012-01-02 15:13:38.6089121

,SYSDATETIMEOFFSET() -- 2012-01-02 15:13:38.6089121 +05:30

,SYSUTCDATETIME() -- 2012-01-02 09:43:38.6399152

,CURRENT_TIMESTAMP -- 2012-01-02 15:13:38.637

,GETDATE() -- 2012-01-02 15:13:38.637

,GETUTCDATE(); -- 2012-01-02 09:43:38.637

-- SQL Server current system date functions with conversions
SELECT CONVERT (datetime, SYSDATETIME()) -- 2012-01-02 15:13:38.640

,CONVERT (datetime, SYSDATETIMEOFFSET()) -- 2012-01-02 15:13:38.647

,CONVERT (datetime, SYSUTCDATETIME()) -- 2012-01-02 09:43:38.647

,CONVERT (datetime, CURRENT_TIMESTAMP) -- 2012-01-02 15:13:38.643

,CONVERT (datetime, GETDATE()) -- 2012-01-02 15:13:38.643

,CONVERT (datetime, GETUTCDATE()); -- 2012-01-02 15:13:38.643

Sequence Generator

CREATE FUNCTION dbo.fnSequenceGenerator  (@Limit INT)

RETURNS @Sequence TABLE(SequentialNumber INT)

AS

  BEGIN

    DECLARE  @RunningValue INT = 1

    WHILE @RunningValue <= @Limit

      BEGIN

        INSERT @Sequence

        VALUES(@RunningValue)

        SET @RunningValue += 1

      END

    RETURN

  END

GO               

-- Test

SELECT * FROM   dbo.fnSequenceGenerator(11)

GO

-- Method 2:

-- SQL cte - Common Table Expression :SQL sequence

-- Create a cte to give the sequence of 0-9

with cteDigits as

(    

select 0 as Digit

union select 1

union select 2

union select 3

union select 4

union select 5

union select 6

union select 7

union select 8

union select 9),

              

-- Create a second cte to generate a million number sequence

cteMillion as (

select SeqNo=hundredT.Digit * 100000+tenT.Digit * 10000 +

       thousands.Digit *1000 + hundreds.Digit * 100 +

       tens.Digit * 10 + ones.Digit + 1

from cteDigits as ones

-- SQL cross join

cross join cteDigits as tens

cross join cteDigits as hundreds

cross join cteDigits as thousands

cross join cteDigits as tenT

cross join cteDigits as hundredT ) 

-- Main query

-- SQL select from cte

select top 1000 SeqNo

from cteMillion

order by SeqNo

go

Listing Files / Directories (Names) Using T-SQL

/*
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
*/
-- Get file list in specified directory (folder)
-- T-SQL command shell - insert exec
CREATE TABLE #FileList (
  Line VARCHAR(512))
DECLARE @Path varchar(256) = 'dir f:\data\'
DECLARE @Command varchar(1024) =  @Path+' /A-D  /B'
PRINT @Command
INSERT #FileList
EXEC MASTER.dbo.xp_cmdshell @Command
DELETE #FileList WHERE  Line IS NULL

SELECT * FROM   #FileList
GO
DROP TABLE #FileList
GO

-- Get directory (subdirectory, folder) list in specified directory (folder)
CREATE TABLE #DirectoryList (
  Line VARCHAR(512))
DECLARE @Path varchar(256) = 'dir f:\data\'
DECLARE @Command varchar(1024) =  @Path+' /A-A  /B'
PRINT @Command
INSERT #DirectoryList
EXEC MASTER.dbo.xp_cmdshell @Command
DELETE #DirectoryList WHERE  Line IS NULL

SELECT * FROM   #DirectoryList
GO
DROP TABLE #DirectoryList
GO

-- List all files in a directory - T-SQL parse string for date and filename
-- Microsoft SQL Server command shell statement - xp_cmdshell
DECLARE @PathName VARCHAR(256) ,
@CMD VARCHAR(512)

CREATE TABLE #CommandShell ( Line VARCHAR(512))

SET @PathName = 'F:\data\download\microsoft\'

SET @CMD = 'DIR ' + @PathName + ' /TC'

PRINT @CMD -- test & debug
-- DIR F:\data\download\microsoft /TC

-- MSSQL insert exec - insert table from stored procedure execution
INSERT INTO #CommandShell
EXEC MASTER..xp_cmdshell @CMD

-- Delete lines not containing filename
DELETE
FROM #CommandShell
WHERE Line NOT LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9] %'
OR Line LIKE '%<DIR>%'
OR Line is null
-- SQL reverse string function - charindex string function
SELECT
FileName = REVERSE( LEFT(REVERSE(Line),CHARINDEX(' ',REVERSE(line))-1 ) ),
CreateDate = LEFT(Line,10)
FROM #CommandShell
ORDER BY FileName
DROP TABLE #CommandShell

GO
------------

Cursor Vs Set Based Operation

-- SQL Server Nested Cursors example - Execution timing setu

DBCC DROPCLEANBUFFERS

DECLARE @StartTime datetime = getdate() 

-- Setup local variables

DECLARE     @IterationID INT,

            @OrderDetail VARCHAR(max),

            @ProductName VARCHAR(10)

-- Setup table variable

DECLARE @Result TABLE (SalesOrderID INT, OrderDetail VARCHAR(max))

             

-- OUTER CURSOR declaration

DECLARE curOrdersForReport CURSOR FOR

SELECT SalesOrderID

FROM Sales.SalesOrderHeader

WHERE Year(OrderDate) = 2004

  AND Month(OrderDate) between 2 and 4

ORDER BY SalesOrderID 

OPEN curOrdersForReport

FETCH NEXT FROM curOrdersForReport INTO @IterationID

PRINT 'OUTER LOOP START'

WHILE (@@FETCH_STATUS = 0)

BEGIN

      SET @OrderDetail = ''

      -- INNER CURSOR declaration

      DECLARE curDetailList CURSOR FOR

      SELECT p.ProductNumber

      FROM Sales.SalesOrderDetail pd

      INNER JOIN Production.Product p

      ON pd.ProductID = p.ProductID

      WHERE pd.SalesOrderID = @IterationID

      ORDER BY SalesOrderDetailID 

      OPEN curDetailList

      FETCH NEXT FROM curDetailList INTO @ProductName

      PRINT 'INNER LOOP START'

      WHILE (@@FETCH_STATUS = 0)

      BEGIN

            SET @OrderDetail += @ProductName + ', '

            FETCH NEXT FROM curDetailList INTO @ProductName

            PRINT 'INNER LOOP'

      END -- inner while

      CLOSE curDetailList

      DEALLOCATE curDetailList               

      -- Truncate trailing comma

      SET @OrderDetail = left(@OrderDetail, len(@OrderDetail)-1)

      INSERT INTO @Result VALUES (@IterationID, @OrderDetail)

      FETCH NEXT FROM curOrdersForReport INTO @IterationID

      PRINT 'OUTER LOOP'

END -- outer while

CLOSE curOrdersForReport

DEALLOCATE curOrdersForReport 

-- Publish results

SELECT * FROM @Result ORDER BY SalesOrderID

-- Timing result

SELECT ExecutionMsec = datediff(millisecond, @StartTime, getdate())

GO

-- 2800 msecs

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

-- Equivalent set-based operations solution

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

-- Execution timing setup

DBCC DROPCLEANBUFFERS

DECLARE @StartTime datetime = getdate()

SELECT  poh.SalesOrderID,

      OrderDetail = Stuff((

      SELECT ', ' + ProductNumber as [text()]

      FROM Sales.SalesOrderDetail pod

      INNER JOIN Production.Product p

      ON pod.ProductID = p.ProductID

      WHERE pod.SalesOrderID = poh.SalesOrderID

      ORDER BY SalesOrderDetailID

      FOR XML PATH ('')), 1, 1, '')

FROM Sales.SalesOrderHeader poh

WHERE Year(OrderDate) = 2004

  AND Month(OrderDate) between 2 and 4

ORDER BY SalesOrderID ; 

-- Timing result

SELECT ExecutionMsec = datediff(millisecond, @StartTime, getdate())

GO

-- 400 msecs 

/* Partial results 

SalesOrderID      OrderDetail

63119             FR-M63S-40

63120             SH-W890-S, SH-W890-M, SH-W890-L

63121             SH-W890-S, SH-W890-M

63122             PD-R347, HL-U509-B, HL-U509

*/

Encrypt Passwords Using T-SQL Functions

-- DROP TABLE dbo.UserLogin

CREATE TABLE dbo.UserLogin (

  UserLoginID INT    IDENTITY ( 1 , 1 )    PRIMARY KEY,

  LoginName   CHAR(30)    NOT NULL,

  [PassWord]  VARBINARY(MAX)    NOT NULL,

  IsActive    BIT    NOT NULL CONSTRAINT DF_UserLogin_IsActive DEFAULT ((1)),

  CreateDate  DATETIME NOT NULL CONSTRAINT DF_UserLogin_CreateDate DEFAULT (getdate()),

  ModifyDate  DATETIME NOT NULL CONSTRAINT DF_UserLogin_ModifyDate DEFAULT (getdate()),

  ModifiedBy  CHAR(6)    NOT NULL CONSTRAINT DF_UserLogin_ModifiedBy DEFAULT ('system'))

GO

-- drop ASYMMETRIC KEY Asym_PassWord

CREATE ASYMMETRIC KEY Asym_PassWord WITH ALGORITHM = RSA_512
ENCRYPTION BY PASSWORD = N'secreT007!'

DECLARE  @CipherString VARBINARY(MAX);

SELECT @CipherString = EncryptByAsymKey(AsymKey_ID('Asym_PassWord'),N'SecretPass!01');

INSERT INTO UserLogin

           (LoginName,

            [PassWord])

VALUES     ('administrator',@CipherString);

GO

DECLARE  @CipherString VARBINARY(MAX);

SELECT @CipherString = EncryptByAsymKey(AsymKey_ID('Asym_PassWord'),N'OperPass99$');

INSERT INTO UserLogin

           (LoginName,

            [PassWord])

VALUES     ('operator',@CipherString);

GO

SELECT *

FROM   UserLogin

GO

-- Following query can be used to test an entered password:

SELECT      LoginName,

            PassWordDecrypted= convert(nvarchar(128),

            DecryptByAsymKey(AsymKey_ID('Asym_PassWord'),

            [PassWord], N'secreT007!' ))

FROM UserLogin

WHERE LoginName = 'operator'

GO

SELECT      LoginName,

            PassWordDecrypted= convert(nvarchar(128),

            DecryptByAsymKey(AsymKey_ID('Asym_PassWord'),

            [PassWord], N'secreT007!' ))

FROM UserLogin

WHERE LoginName = 'administrator'

GO

-- Cleanup

DROP TABLE dbo.UserLogin