Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

given a date, tell me how many seconds from 1900 Expand / Collapse
Author
Message
Posted Wednesday, May 8, 2013 11:06 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 1:39 PM
Points: 245, Visits: 735
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.
Post #1450701
Posted Wednesday, May 8, 2013 11:37 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:34 PM
Points: 3,122, Visits: 11,408
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.

Post #1450712
Posted Wednesday, May 8, 2013 11:39 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 42,470, Visits: 35,541
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 2008, MVP
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

Post #1450713
Posted Wednesday, May 8, 2013 11:55 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 1:39 PM
Points: 245, Visits: 735
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.
Post #1450724
Posted Wednesday, May 8, 2013 11:58 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 1:39 PM
Points: 245, Visits: 735
Michael, really liked your solution also.

<><
Livin' down on the cube farm. Left, left, then a right.
Post #1450727
Posted Wednesday, May 8, 2013 12:37 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:03 PM
Points: 4,388, Visits: 9,510
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
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1450745
Posted Wednesday, May 8, 2013 1:07 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:34 PM
Points: 3,122, Visits: 11,408
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.







Post #1450766
Posted Wednesday, May 8, 2013 2:27 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:19 AM
Points: 4,320, Visits: 6,113
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 at GMail
Post #1450793
Posted Wednesday, May 8, 2013 2:45 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:34 PM
Points: 3,122, Visits: 11,408
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



Post #1450802
Posted Thursday, May 9, 2013 4:13 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-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)
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,'č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


<?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>


Post #1450986
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse