Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 given a date, tell me how many seconds from 1900 Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, May 8, 2013 11:06 AM
 SSC Veteran Group: General Forum Members Last Login: Friday, March 20, 2015 8:32 AM Points: 253, Visits: 758
 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 datetimeset @d = '01/01/1900 00:00:00'select @dselect convert(int, @d)-----------------------1900-01-01 00:00:00.000(1 row(s) affected)-----------0(1 row(s) affected)declare @d datetimeset @d = '01/02/1900 00:00:00'select @dselect 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. <>
Post #1450701
 Posted Wednesday, May 8, 2013 11:37 AM
 Hall of Fame Group: General Forum Members Last Login: Saturday, October 24, 2015 2:31 AM Points: 3,158, Visits: 11,771
 `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' ) aorder by a.DT`Results:`DT SecondsSince_19000101----------------------- ---------------------1753-01-01 00:00:00.000 -46388160001899-12-31 12:01:01.997 -431392013-05-08 15:07:18.380 35770144389999-12-31 23:59:59.997 255611289599`Edited: Replaced 86000 with correct value of 86400 for number of seconds in a day.
Post #1450712
 Posted Wednesday, May 8, 2013 11:39 AM
 SSC-Forever Group: General Forum Members Last Login: Saturday, December 3, 2016 5:18 AM Points: 45,619, Visits: 44,147
 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 ShawMicrosoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enterWe stand on the bridge and no one may pass
Post #1450713
 Posted Wednesday, May 8, 2013 11:55 AM
 SSC Veteran Group: General Forum Members Last Login: Friday, March 20, 2015 8:32 AM Points: 253, Visits: 758
 Thanks Gail. I had looked at the date functions. Wish I had extrapolated out that I could use datediff(). <>
Post #1450724
 Posted Wednesday, May 8, 2013 11:58 AM
 SSC Veteran Group: General Forum Members Last Login: Friday, March 20, 2015 8:32 AM Points: 253, Visits: 758
 Michael, really liked your solution also. <>
Post #1450727
 Posted Wednesday, May 8, 2013 12:37 PM
 SSCarpal Tunnel Group: General Forum Members Last Login: Yesterday @ 6:26 PM Points: 4,377, Visits: 9,708
 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' ) aorder by a.DT`Results:`DT SecondsSince_19000101----------------------- ---------------------1753-01-01 00:00:00.000 -46173400001899-12-31 12:01:01.997 -427392013-05-08 13:35:03.717 35604489039999-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 WilliamsProblems are opportunities brilliantly disguised as insurmountable obstacles.How to post questions to get better answers fasterManaging Transaction Logs
Post #1450745
 Posted Wednesday, May 8, 2013 1:07 PM
 Hall of Fame Group: General Forum Members Last Login: Saturday, October 24, 2015 2:31 AM Points: 3,158, Visits: 11,771
 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' ) aorder by a.DT`Results:`DT SecondsSince_19000101----------------------- ---------------------1753-01-01 00:00:00.000 -46173400001899-12-31 12:01:01.997 -427392013-05-08 13:35:03.717 35604489039999-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.
Post #1450766
 Posted Wednesday, May 8, 2013 2:27 PM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 12:11 PM Points: 5,654, Visits: 8,178
 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' ) aorder by a.DT`I am curious about the occasional difference, but don't have time to investigate... Best,Kevin G. BolesSQL Server ConsultantSQL MVP 2007-2012TheSQLGuru at GMail
Post #1450793
 Posted Wednesday, May 8, 2013 2:45 PM
 Hall of Fame Group: General Forum Members Last Login: Saturday, October 24, 2015 2:31 AM Points: 3,158, Visits: 11,771
 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' ) aorder 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`
Post #1450802
 Posted Thursday, May 9, 2013 4:13 AM
 SSC-Addicted Group: General Forum Members Last Login: Sunday, September 29, 2013 1:24 AM Points: 429, Visits: 1,721
 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) ASBEGIN 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,'čeština','Czech'), (13,'ymd',1,'magyar','Hungarian'), (14,'dmy',1,'polski','Polish'), (15,'dmy',1,'română','Romanian'), (16,'ymd',1,'hrvatski','Croatian'), (17,'dmy',1,'slovenčina','Slovak'), (18,'dmy',1,'slovenski','Slovenian'), (19,'dmy',1,'ελληνικά','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,'lietuvių','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` 'SET DATEFORMAT dmy' 'SET LANGUAGE Greek' 1967-05-02 00:00:00.000 2013-11-22 00:00:00.000 `
Post #1450986

 Permissions