Converting DateTime to Ticks or Ticks to DateTime using SQL Server function

Description : In this function how to convert a datetime to ticks in sql function and ticks to DateTime

Below function for Convert DateTime to Ticks

CREATE FUNCTION dbo.ConvertDateTimeToTicks (@DateTime datetime)
  RETURNS bigint
AS
BEGIN
    RETURN DATEDIFF_BIG( microsecond, '00010101', @DateTime ) * 10 +
    (DATEPART( NANOSECOND, @DateTime ) % 1000) / 100;
END

Call function

SELECT dbo.ConvertDateTimeToTicks('2018-02-26 14:05:24.250')

Result : 636552507242500000

Below function for convert ticks to datetime

CREATE FUNCTION dbo.ConvertTicksToDateTime ( @Ticks bigint )
  RETURNS datetime
AS
BEGIN
    DECLARE @DateTime datetime2 = '00010101';
    SET @DateTime = DATEADD( DAY, @Ticks / 864000000000, @DateTime );
    SET @DateTime = DATEADD( SECOND, ( @Ticks % 864000000000) / 10000000, @DateTime );
    RETURN DATEADD( NANOSECOND, ( @Ticks % 10000000 ) * 100, @DateTime );
END

SELECT dbo.ConvertDateTimeToTicks(636552507242500000)

Result : 2018-02-26 14:05:24.2500000

Datetime convert in SQL Server

Description : In this post how to convert date in different formats in sql server

SELECT CONVERT(VARCHAR, GETDATE(), 100)
Result : MON DD YYYY HH:MMAM

------------------------------------------
SELECT CONVERT(VARCHAR, GETDATE(), 101)
Result : MM/DD/YYYY – 10/02/2008                 
------------------------------------------

SELECT CONVERT(VARCHAR, GETDATE(), 102)
Result : YYYY.MM.DD – 2008.10.02          
------------------------------------------

SELECT CONVERT(VARCHAR, GETDATE(), 103)
Result : DD/MM/YYYY
------------------------------------------

SELECT CONVERT(VARCHAR, GETDATE(), 104)
Result : DD.MM.YYYY
------------------------------------------

SELECT CONVERT(VARCHAR, GETDATE(), 105)
Result : DD-MM-YYYY
------------------------------------------

SELECT CONVERT(VARCHAR, GETDATE(), 106)
Result : DD MON YYYY
------------------------------------------

SELECT CONVERT(VARCHAR, GETDATE(), 107)
Result : MON DD, YYYY
------------------------------------------

SELECT CONVERT(VARCHAR, GETDATE(), 108)
Result : HH:MM:SS
------------------------------------------

SELECT CONVERT(VARCHAR, GETDATE(), 109)
Result : MON DD YYYY HH:MM:SS:MMMAM (OR PM)
------------------------------------------

SELECT CONVERT(VARCHAR, GETDATE(), 110)
Result : MM-DD-YYYY
------------------------------------------

SELECT CONVERT(VARCHAR, GETDATE(), 111)
Result : YYYY/MM/DD
------------------------------------------

SELECT CONVERT(VARCHAR, GETDATE(), 112)
Result : YYYYMMDD
------------------------------------------

SELECT CONVERT(VARCHAR, GETDATE(), 113)
Result : DD MON YYYY HH:MM:SS:MMM
------------------------------------------

SELECT CONVERT(VARCHAR, GETDATE(), 114)
Result : HH:MM:SS:MMM(24H)
------------------------------------------

SELECT CONVERT(VARCHAR, GETDATE(), 120)
Result : YYYY-MM-DD HH:MM:SS(24H)
------------------------------------------

SELECT CONVERT(VARCHAR, GETDATE(), 121)
Result : YYYY-MM-DD HH:MM:SS.MMM
------------------------------------------

SELECT CONVERT(VARCHAR, GETDATE(), 126)
Result : YYYY-MM-DDTHH:MM:SS.MMM