How to convert DateTime to a number with a precision greater than days in T-SQL

Question

Both queries below translates to the same number  SELECT CONVERT bigint CONVERT datetime  2009-06-15 15 00 00    SELECT CAST CONVERT datetime  2009-06-15 23 01 00   as bigint    Result  39978 39978   The generated number will be different only if the days are different  There is any way to convert the DateTime to a more precise number  as we do in  NET with the  Ticks property   I need at least a minute precision

User · Answer

SELECT CAST CONVERT datetime  2009-06-15 23 01 00   as float    yields 39977 9590277778

User · Answer

CAST to a float or decimal instead of an int bigint   The integer portion  before the decimal point  represents the number of whole days  After the decimal are the fractional days  i e   time

User · Answer

If the purpose of this is to create a unique value from the date  here is what I would do  DECLARE  ts TIMESTAMP  SET  ts   CAST getdate   AS TIMESTAMP  SELECT  ts   This gets the date and declares it as a simple timestamp

User · Answer

Use DateDiff for this   DateDiff  DatePart   StartDate   EndDate    DatePart goes from Year down to Nanosecond   More here   http   msdn microsoft com en-us library ms189794 aspx

User · Answer

And here is a bigint version of the same   DECLARE  ts BIGINT  SET  ts   CAST CAST getdate   AS TIMESTAMP  AS BIGINT  SELECT  ts

User · Answer

You can use T-SQL to convert the date before it gets to your  NET program  This often is simpler if you don t need to do additional date conversion in your  NET program   DECLARE  Date DATETIME   Getdate   DECLARE  DateInt INT   CONVERT VARCHAR 30    Date  112  DECLARE  TimeInt INT   REPLACE CONVERT VARCHAR 30    Date  108            DECLARE  DateTimeInt BIGINT   CONVERT VARCHAR 30    Date  112    REPLACE CONVERT VARCHAR 30    Date  108            SELECT  Date as Date   DateInt DateInt   TimeInt TimeInt   DateTimeInt DateTimeInt     Date                    DateInt     TimeInt     DateTimeInt ------------------------- ----------- ----------- -------------------- 2013-01-07 15 08 21 680 20130107    150821      20130107150821

User · Answer

DECLARE  baseTicks AS BIGINT  SET  baseTicks   599266080000000000  --  ticks up to 1900-01-01  DECLARE  ticksPerDay AS BIGINT  SET  ticksPerDay   864000000000   SELECT CAST  baseTicks     ticksPerDay   CAST GETDATE   AS FLOAT   AS BIGINT  AS currentDateTicks

User · Answer

Well  I would do it like this   select datediff minute  1990-1-1  datetime    where  1990-1-1  is an arbitrary base datetime

[sql-server-2005] How to convert DateTime to a number with a precision greater than days in T-SQL?

Examples related to sql-server-2005

Examples related to tsql

Examples related to datetime

Examples related to integer

Examples related to type-conversion