DATEFROMPARTS –Construct a Date/Datetime with SQL Server 2012

--SQL Server 2012

DATEFROMPARTS ( year, month, day )
DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )
DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )

--Samples:
SELECT DATEFROMPARTS ( 2013, 3, 31 )
SELECT DATEFROMPARTS ( 2012, 2, 29 )
SELECT DATETIMEFROMPARTS ( 2010, 12, 31, 23, 59, 59, 0 )
SELECT DATETIME2FROMPARTS ( 2011, 8, 15, 14, 23, 44, 50, 2 );



Advertisements

A DENSE_RANK() Sample (T-SQL)

if object_id('test','U') is not null
Drop table test

Create table test (t int, ID int)
Insert into test values (6,1),
(6,	2),
(6,	3),
(6,	4),
(6,	5),
(11,	6),
(11,	7),
(5,	8),
(5,	9),
(10,	10),
(10,	11),
(4,	12),
(4,	13),
(9,	14),
(3,	15),
(3,	16),
(8,	17),
(2,	18),
(2,	19),
(7,	20)

;with mycte as
(select *,  row_number() over(order by ID) - row_number() over(partition by t order by ID) rnDelta from test
)
Select dense_rank()Over(  order by rndelta ) X, T, id from mycte
order by id
/*
X	T	id
1	6	1
1	6	2
1	6	3
1	6	4
1	6	5
2	11	6
2	11	7
3	5	8
3	5	9
4	10	10
4	10	11
5	4	12
5	4	13
6	9	14
7	3	15
7	3	16
8	8	17
9	2	18
9	2	19
10	7	20

*/
--http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/b14e4533-14c8-45fc-b50a-565a36bbb9da


Bitwise T-SQL Sample


CREATE TABLE Tree_Contacts  (Nodename varchar(10), options int)
INSERT Tree_Contacts  values('Node1', 1)
 ,('Node2', 2)
 ,('Node3', 3)
,('Node4', 7)
,('Node4', 6)

Select a.Nodename
    , Replace( Replace( Replace(( 
        Select col 
        From ( VALUES (1,'Access to Client'),(2,'Access to ADMIN'),(4,'Access to web')) AS b(options, col)
        Where a.options & b.options <> 0 
        Order By b.col For XML Raw)
        , '"/><row col="', ', '), '<row col="', ''), '"/>', '') 
        As 'ApplicationAccesstest'
From Tree_Contacts  a
Order By a.Nodename;

Drop table Tree_Contacts 


Schedule a Job To Run SSIS Package to Delete Old Files with Robocopy

Step 1: Create a bat file (saved as TheDeleteFile.bat from notepad in this example)  in the following format (use your own file paths)

IF NOT EXIST \\myShare\SQLData\empty mkdir \\myShare\SQLBackups\empty
Robocopy \\myShare\SQLData\backups \\myShare\SQLData\empty /e /MOVE /create /MINAGE:15 /LOG+:C:\jobfiles\log\%date:~-4,4%%date:~-10,2%%date:~-7,2%_log.txt
rmdir \\myShare\SQLData\empty /s /q

 

Any files in the  \\myShare\SQLData\backups folder that are 15 days old will be deleted. You can change the number 15 to the number you desire.

 

Step 2:  Design an SSIS Package with  Execute Process Task component:

The major Process Properties:
RequiredFullFileName: True
Executable: C:\myjobfile\TheDeleteFile.bat
Arguments: ..\bat
WorkingDirectory: C:\myjobfile

 

Step 3: Set up a job from SSMS to run the SQL Server Intergration Package designed from above step and schedule a time to run this job.

 

References:

http://texhex.blogspot.com/2009/09/misusing-robocopy-to-delete-old-files.html

http://www.codesingh.com/2009/08/using-robocopy-to-delete-old-files-from.html

http://blogs.msdn.com/b/jjameson/archive/2009/11/07/using-robocopy-to-move-files-and-folders.aspx

 


Calculate Age From Birthdate Column (T-SQL)

You can have a few options to calculate age in years from a date of birth column. When the table gets bigger, the performance can be an issue.
Most important thing you should do is to make sure that you have appropriate indexes in place.
The following scripts have the query to retrieve age from the table and the two indexes I have created on the table.



USE myDB
GO
--CREATE NONCLUSTERED INDEX idx_Birthday
--ON [dbo].[myTable] ([birthdate])
--GO

 
--CREATE NONCLUSTERED INDEX idx_Birthday_myTableid
--ON [dbo].[myTable] ([birthdate])
--INCLUDE ([myTableid])
--GO

 

--Inline 1
SELECT [birthdate]
  , DATEDIFF(yy, [birthdate], current_timestamp) - CASE WHEN
(MONTH([birthdate]) > MONTH(current_timestamp)) OR (MONTH([birthdate]) = MONTH(current_timestamp)
AND DAY([birthdate]) > DAY(current_timestamp)) THEN 1 ELSE 0 END
  FROM  [dbo].[myTable]
  WHERE [birthdate] is not null
--order by myTableid

--Inline 2
  SELECT myTableid, [birthdate]
  , CASE WHEN
 (DATEADD(year,DATEDIFF(year, [birthdate]  ,current_timestamp) , [birthdate]) > current_timestamp)
THEN DATEDIFF(year, [birthdate]  ,current_timestamp) -1
ELSE DATEDIFF(year, [birthdate]  ,current_timestamp) END
  FROM  [dbo].[myTable]
  WHERE [birthdate] is not null 
 --order by myTableid

 --Inline 3
 SELECT  [birthdate],FLOOR((CAST (current_timestamp AS INT) - CAST([birthdate] AS INT)) / 365.25) AS Age
  FROM  [dbo].[myTable]
  WHERE [birthdate] is not null
--order by myTableid


--Scalar UDF
  Select  [birthdate], [dbo].[udfGetAge]([birthdate])    
  FROM  [dbo].[myTable]
  WHERE [birthdate] is not null
  --order by myTableid


--Computed Column
--Cannot set Persisted because current_timestamp is not  deterministic
  Select  [birthdate], Age   
  FROM  [dbo].[myTable]
  WHERE [birthdate] is not null


  

 

Find a Day From a Sequenced Weekday in a Selected Month


DECLARE @tbl TABLE (Yr INT,Mon VARCHAR(50),Dy VARCHAR(50),Dyno INT)

INSERT INTO @tbl(Yr, Mon, Dy, Dyno)
SELECT 2010,'Jan','Sun',2 UNION ALL
SELECT 2005,'Jan','Mon',3 UNION ALL
SELECT 1995,'Feb','Sun',1 UNION ALL
SELECT 2000,'Feb','Wed',4 UNION ALL
SELECT 1982,'Mar','Tue',2 

;with mycte as
(
select Yr, Mon, Dy, Dyno, 
CAST(Mon +'1 '+ cast(Yr as char(4)) as datetime) DayOne, 
datepart(weekday,CAST(Mon +'1 '+ cast(Yr as char(4)) as datetime)) as WeekNumDayOne, 
CASE Dy
WHEN  'Sun' THEN 1
WHEN  'Mon' THEN 2  
WHEN  'Tue' THEN 3
WHEN  'Wed' THEN 4
WHEN  'Thu' THEN 5
WHEN  'Fri' THEN 6
WHEN  'Sat' THEN 7
END WeekNum
FROM @tbl)

SELECT Yr, Mon, Dy, Dyno
,dateadd(day, (Dyno-case when WeekNumDayOne>WeekNum THEN 0 Else 1 END)*7-(WeekNumDayOne - WeekNum), DayOne) as dt
FROM mycte
Order by yr
/*

Yr	Mon	Dy	Dyno	dt
1982	Mar	Tue	2	1982-03-09 00:00:00.000
1995	Feb	Sun	1	1995-02-05 00:00:00.000
2000	Feb	Wed	4	2000-02-23 00:00:00.000
2005	Jan	Mon	3	2005-01-17 00:00:00.000
2010	Jan	Sun	2	2010-01-10 00:00:00.000

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/dbfcdeb0-0715-461a-a7c8-49ecee77518f
*/

Convert Day of Week Name to Day Number

SELECT CASE LEFt(DATENAME(DW,getdate()),3)
WHEN ‘Sun’ THEN 1
WHEN ‘Mon’ THEN 2
WHEN ‘Tue’ THEN 3
WHEN ‘Wed’ THEN 4
WHEN ‘Thu’ THEN 5
WHEN ‘Fri’ THEN 6
WHEN ‘Sat’ THEN 7
END WeekNum

Convert to week name from week number:

https://jingyangli.wordpress.com/2010/06/02/convert-day-of-week-number-to-day-name/