SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


given a date, tell me how many seconds from 1900


given a date, tell me how many seconds from 1900

Author
Message
Tobar
Tobar
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1031 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 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.
Michael Valentine Jones
Michael Valentine Jones
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14091 Visits: 11848

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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218267 Visits: 46278
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


Tobar
Tobar
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1031 Visits: 758
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.
Tobar
Tobar
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1031 Visits: 758
Michael, really liked your solution also.

<><
Livin' down on the cube farm. Left, left, then a right.
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18607 Visits: 10042
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 opportunities brilliantly disguised as insurmountable obstacles.

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

Michael Valentine Jones
Michael Valentine Jones
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14091 Visits: 11848
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
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31412 Visits: 8670
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
Michael Valentine Jones
Michael Valentine Jones
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14091 Visits: 11848
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


Steven Willis
Steven Willis
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1965 Visits: 1721
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>



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search