given a date, tell me how many seconds from 1900

  • Other than breaking a date string into its parts and multiplying them by the appropriate number of seconds is there a, simple, way to display a date as the number of seconds since 01/01/1900? I thought I was on to something but ...

    declare @d datetime

    set @d = '01/01/1900 00:00:00'

    select @d

    select convert(int, @d)

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

    1900-01-01 00:00:00.000

    (1 row(s) affected)

    -----------

    0

    (1 row(s) affected)

    declare @d datetime

    set @d = '01/02/1900 00:00:00'

    select @d

    select convert(int, @d)

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

    1900-01-02 00:00:00.000

    (1 row(s) affected)

    -----------

    1

    (1 row(s) affected) seems like it is thinking in days. Do this and then calculating seems tedious, if not error prone.

    <><
    Livin' down on the cube farm. Left, left, then a right.

  • select

    DT,

    SecondsSince_19000101 =

    -- seconds for whole days

    (datediff(dd,0,a.DT)*000000000086400)+

    -- seconds since start of day

    datediff(ss,dateadd(dd,datediff(dd,0,a.DT),0),a.DT)

    from

    ( -- test data

    select DT = getdate()union all

    select DT = '99991231 23:59:59.997'union all

    select DT = '18991231 12:01:01.997'union all

    select DT = '17530101 00:00:00.000'

    ) a

    order by

    a.DT

    Results:

    DT SecondsSince_19000101

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

    1753-01-01 00:00:00.000 -4638816000

    1899-12-31 12:01:01.997 -43139

    2013-05-08 15:07:18.380 3577014438

    9999-12-31 23:59:59.997 255611289599

    Edited: Replaced 86000 with correct value of 86400 for number of seconds in a day.

  • You're looking for the function datediff, however it'll overflow for a value that far back as datediff returns an int and 1900/01/01 is more than MAXINT seconds ago.

    This works.

    SELECT CAST(datediff(hour,'1900/01/01',GETDATE()) AS BIGINT)*3600 AS SecondsSince1900

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks Gail. I had looked at the date functions. Wish I had extrapolated out that I could use datediff().

    <><
    Livin' down on the cube farm. Left, left, then a right.

  • Michael, really liked your solution also.

    <><
    Livin' down on the cube farm. Left, left, then a right.

  • Michael Valentine Jones (5/8/2013)


    select

    DT,

    SecondsSince_19000101 =

    -- seconds for whole days as bigint

    (datediff(dd,0,a.DT)*000000000086000)+

    -- seconds since start of day

    datediff(ss,dateadd(dd,datediff(dd,0,a.DT),0),a.DT)

    from

    ( -- test data

    select DT = getdate()union all

    select DT = '99991231 23:59:59.997'union all

    select DT = '18991231 12:01:01.997'union all

    select DT = '17530101 00:00:00.000'

    ) a

    order by

    a.DT

    Results:

    DT SecondsSince_19000101

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

    1753-01-01 00:00:00.000 -4617340000

    1899-12-31 12:01:01.997 -42739

    2013-05-08 13:35:03.717 3560448903

    9999-12-31 23:59:59.997 254427904399

    Michael, can you explain why you used 86000 instead of 86400? There are 86,400 seconds in a day - and it appears your calculation will be off because of that.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams 3188 (5/8/2013)


    Michael Valentine Jones (5/8/2013)


    select

    DT,

    SecondsSince_19000101 =

    -- seconds for whole days as bigint

    (datediff(dd,0,a.DT)*000000000086000)+

    -- seconds since start of day

    datediff(ss,dateadd(dd,datediff(dd,0,a.DT),0),a.DT)

    from

    ( -- test data

    select DT = getdate()union all

    select DT = '99991231 23:59:59.997'union all

    select DT = '18991231 12:01:01.997'union all

    select DT = '17530101 00:00:00.000'

    ) a

    order by

    a.DT

    Results:

    DT SecondsSince_19000101

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

    1753-01-01 00:00:00.000 -4617340000

    1899-12-31 12:01:01.997 -42739

    2013-05-08 13:35:03.717 3560448903

    9999-12-31 23:59:59.997 254427904399

    Michael, can you explain why you used 86000 instead of 86400? There are 86,400 seconds in a day - and it appears your calculation will be off because of that.

    Easy to explain. I just typed it wrong. :blush:

  • Here is an interesting solution that could be more efficient (didn't test that):

    select

    DT,

    CAST(dt AS decimal(29,15))*86400 AS decdtsec2,

    SecondsSince_19000101 =

    -- seconds for whole days

    (datediff(dd,0,a.DT)*000000000086400)+

    -- seconds since start of day

    datediff(ss,dateadd(dd,datediff(dd,0,a.DT),0),a.DT)

    from

    ( -- test data

    select DT = getdate()union all

    select DT = '99991231 23:59:59.997'union all

    select DT = '18991231 12:01:01.997'union all

    select DT = '17530101 00:00:00.000'

    ) a

    order by

    a.DT

    I am curious about the occasional difference, but don't have time to investigate...

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • TheSQLGuru (5/8/2013)


    Here is an interesting solution that could be more efficient (didn't test that):

    select

    DT,

    CAST(dt AS decimal(29,15))*86400 AS decdtsec2,

    SecondsSince_19000101 =

    -- seconds for whole days

    (datediff(dd,0,a.DT)*000000000086400)+

    -- seconds since start of day

    datediff(ss,dateadd(dd,datediff(dd,0,a.DT),0),a.DT)

    from

    ( -- test data

    select DT = getdate()union all

    select DT = '99991231 23:59:59.997'union all

    select DT = '18991231 12:01:01.997'union all

    select DT = '17530101 00:00:00.000'

    ) a

    order by

    a.DT

    I am curious about the occasional difference, but don't have time to investigate...

    At the very least, I would call this an unsupported/undocumented method for manipulating dates in SQL Server:

    CAST(dt AS decimal(29,15))*86400 AS decdtsec2

  • Just for fun I took this ball and ran with it...here's a procedure I whipped up that counts the number of periods between any two dates for years, quarters, weeks, etc. I then got totally carried away with it and threw in support for the list of languages on my machine. By putting in a language code the procedure will do a simple lookup to get the proper DATEFORMAT setting (DMY,MDY, etc). It still needs some heavy-duty testing, but it was a good brain teaser.

    CREATE PROCEDURE dbo.GetDateTimeIntervals

    @LanguageID INT

    ,@StartDate DATETIME

    ,@EndDate DATETIME

    ,@TimeInterval NVARCHAR(50)

    AS

    BEGIN

    SET NOCOUNT ON

    SET LANGUAGE English

    SET DATEFORMAT MDY

    IF OBJECT_ID('tempdb..#LanguageXref') IS NOT NULL

    DROP TABLE #LanguageXref

    CREATE TABLE #LanguageXref (

    [LangID] INT NOT NULL,

    [dateformat] CHAR(3) NULL,

    [datefirst] INT NULL,

    [name] NVARCHAR(50),

    [alias] NVARCHAR(50),

    PRIMARY KEY ([LangID]))

    DECLARE

    @LangID INT

    ,@DateFormat CHAR(3)

    ,@Alias NVARCHAR(50)

    ,@CurrDate DATETIME

    SET @CurrDate = (SELECT GETDATE())

    IF NULLIF(@StartDate,'') IS NULL

    SET @StartDate = CAST('1900-01-01' AS DATETIME)

    IF NULLIF(@EndDate,'') IS NULL

    SET @EndDate = CAST(@CurrDate AS DATETIME)

    SET @LangID = @LanguageID

    INSERT INTO #LanguageXref

    SELECT * FROM (

    VALUES

    (0,'mdy',7,'us_english','English'),

    (1,'dmy',1,'Deutsch','German'),

    (2,'dmy',1,'Français','French'),

    (3,'ymd',7,'???','Japanese'),

    (4,'dmy',1,'Dansk','Danish'),

    (5,'dmy',1,'Español','Spanish'),

    (6,'dmy',1,'Italiano','Italian'),

    (7,'dmy',1,'Nederlands','Dutch'),

    (8,'dmy',1,'Norsk','Norwegian'),

    (9,'dmy',7,'Português','Portuguese'),

    (10,'dmy',1,'Suomi','Finnish'),

    (11,'ymd',1,'Svenska','Swedish'),

    (12,'dmy',1,'ceština','Czech'),

    (13,'ymd',1,'magyar','Hungarian'),

    (14,'dmy',1,'polski','Polish'),

    (15,'dmy',1,'româna','Romanian'),

    (16,'ymd',1,'hrvatski','Croatian'),

    (17,'dmy',1,'slovencina','Slovak'),

    (18,'dmy',1,'slovenski','Slovenian'),

    (19,'dmy',1,'e???????','Greek'),

    (20,'dmy',1,'?????????','Bulgarian'),

    (21,'dmy',1,'???????','Russian'),

    (22,'dmy',1,'Türkçe','Turkish'),

    (23,'dmy',1,'British','British English'),

    (24,'dmy',1,'eesti','Estonian'),

    (25,'ymd',1,'latviešu','Latvian'),

    (26,'ymd',1,'lietuviu','Lithuanian'),

    (27,'dmy',7,'Português (Brasil)','Brazilian'),

    (28,'ymd',7,'????','Traditional Chinese'),

    (29,'ymd',7,'???','Korean'),

    (30,'ymd',7,'????','Simplified Chinese'),

    (31,'dmy',1,'Arabic','Arabic'),

    (32,'dmy',7,'???','Thai')) AS vtable

    ([langid],[dateformat],[datefirst],[name],[alias])

    SET @DateFormat = ''

    SET @Alias = N''

    SELECT

    @DateFormat = [dateformat]

    ,@Alias = [alias]

    FROM

    #LanguageXref

    WHERE

    [LangID] = @LangID

    DECLARE

    @dts DATETIME

    ,@dte DATETIME

    ,@ddif NVARCHAR(20)

    ,@ti NVARCHAR(255)

    SET @dts = ''

    SET @dte = ''

    SET @ti = ''

    BEGIN

    SELECT QUOTENAME('SET DATEFORMAT '+@DateFormat,'''') AS [DateFormat]

    SELECT QUOTENAME('SET LANGUAGE '+@Alias,'''') AS [Language]

    SET @dts = @StartDate

    SELECT CAST(@StartDate AS DATETIME) AS StartDate

    SET @dte = @EndDate

    SELECT CAST(@EndDate AS DATETIME) AS EndDate;

    DECLARE

    @ParmDefinition NVARCHAR(100)

    ,@result BIGINT

    SET @ti = QUOTENAME(N'SELECT @result = DATEDIFF('+@TimeInterval+','''+CONVERT(NVARCHAR(30),@EndDate,121)+''','''+CONVERT(NVARCHAR(30),@StartDate,121)+''')')

    SET @ParmDefinition = N'@result BIGINT OUTPUT'

    SET @ti = REPLACE(REPLACE(@ti,'[',''),']','')

    EXECUTE sp_executesql @ti, @ParmDefinition, @result = @ddif OUTPUT;

    IF @TimeInterval IN ('y','yy','yyyy') SET @TimeInterval = 'YEAR'

    IF @TimeInterval IN ('q','qq') SET @TimeInterval = 'QUARTER'

    IF @TimeInterval IN ('m','mm') SET @TimeInterval = 'MONTH'

    IF @TimeInterval IN ('wk','ww') SET @TimeInterval = 'WEEK'

    IF @TimeInterval IN ('dd','dy','d') SET @TimeInterval = 'DAY'

    IF @TimeInterval IN ('hh') SET @TimeInterval = 'HOUR'

    IF @TimeInterval IN ('mi') SET @TimeInterval = 'MINUTE'

    IF @TimeInterval IN ('s','ss') SET @TimeInterval = 'SECOND'

    IF @TimeInterval IN ('ms') SET @TimeInterval = 'MILLISECOND'

    IF @TimeInterval IN ('n') SET @TimeInterval = 'NANOSECOND'

    IF @TimeInterval IN ('weekday','dw') SET @TimeInterval = 'WEEKDAY'

    IF @TimeInterval IN

    ('YEAR','QUARTER','MONTH','WEEK','DAY','HOUR','MINUTE','SECOND','MILLISECOND',

    'y','yy','yyyy','qq','q','mm','m','wk','ww','dd','d','dy','weekday','dw','hh',

    'mi','n','ss','s','ms')

    BEGIN

    SELECT QUOTENAME(@ddif+' '+UPPER(@TimeInterval)+'(S)','''') AS [Time Units];

    END

    SET DATEFORMAT MDY

    SET LANGUAGE English

    END

    /*

    EXEC dbo.SetLanguage 0,'2/5/1967','2013-22-11','yy'

    EXEC dbo.SetLanguage 1,'2/5/1967','2001/7/5','qq'

    EXEC dbo.SetLanguage 2,'2/5/1967','2013-22-11','mm'

    EXEC dbo.SetLanguage 3,'2/5/1967','2013-22-11','wk'

    EXEC dbo.SetLanguage 11,'2/5/1967','2013-22-11','dd'

    EXEC dbo.SetLanguage 14,'2/5/1967','2013-22-11','hh'

    EXEC dbo.SetLanguage 19,'2/5/1967','2013-22-11','ss'

    EXEC dbo.SetLanguage 32,'2/5/1967','2013-22-11','dw'

    EXEC dbo.SetLanguage 0,'1967-05-08','','yy'

    EXEC dbo.SetLanguage 0,'','1967-05-08','yy'

    EXEC dbo.SetLanguage 0,'','','yy'

    */

    END

    Sample Output

    <?xml version="1.0" ?>

    <RESULTS1>

    <RECORD>

    <DateFormat>'SET DATEFORMAT dmy'</DateFormat>

    </RECORD>

    </RESULTS1>

    <RESULTS2>

    <RECORD>

    <Language>'SET LANGUAGE Greek'</Language>

    </RECORD>

    </RESULTS2>

    <RESULTS3>

    <RECORD>

    <StartDate>1967-05-02 00:00:00.000</StartDate>

    </RECORD>

    </RESULTS3>

    <RESULTS4>

    <RECORD>

    <EndDate>2013-11-22 00:00:00.000</EndDate>

    </RECORD>

    </RESULTS4>

    <RESULTS5>

    <RECORD>

    <Time Units>'1469318400 SECOND(S)'</Time Units>

    </RECORD>

    </RESULTS5>

  • Steven, Glad you got some exercise. 😛

    Let me guess, you turned 46 earlier this year ('2/5/1967'). 😀

    The funny thing is, this request was me anticipating a requirement based on observed values. Turns out what is needed is already canned within Sql Server, albeit, canned obtusely, and "what where you thinkingly". :blink:

    <><
    Livin' down on the cube farm. Left, left, then a right.

  • :laugh: I wish I was still 46!

    When I saw your question last night I remembered a similar scenario concerning UniData date conversions on which I made a post.

    UniData date function.

    UniData dates use a boundary of 31 Dec 1967 so I guess that year stuck in my head. UniData dates are based on the number of seconds before or after 31 Dec 1967 so that earlier post would have been pretty close to a solution as it was by just adding two sets of numbers.

    What got me going this time though really didn't have much to do with the number of seconds since 1900. Rather, I wanted to see if I could incorporate SET commands within a function. The original UniData function is an inline TVF. So I took a dive to see if there was some method for making SET DATEFORMAT and SET LANGUAGE work in an inline TVF. (Alas, I think the answer is no.) So the script I had to settle for required a stored procedure.

    But I learned a new trick in the process using QUOTENAME as a method for executing a SET command in a SELECT statement like this:

    SELECT QUOTENAME('SET DATEFORMAT '+@DateFormat,'''')

    So perhaps it really is possible to do a SET within a function.

     

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply