May 8, 2013 at 11:06 am
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.
May 8, 2013 at 11:37 am
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.
May 8, 2013 at 11:39 am
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
May 8, 2013 at 11:55 am
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.
May 8, 2013 at 11:58 am
Michael, really liked your solution also.
<><
Livin' down on the cube farm. Left, left, then a right.
May 8, 2013 at 12:37 pm
Michael Valentine Jones (5/8/2013)
selectDT,
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
May 8, 2013 at 1:07 pm
Jeffrey Williams 3188 (5/8/2013)
Michael Valentine Jones (5/8/2013)
selectDT,
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:
May 8, 2013 at 2:27 pm
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
May 8, 2013 at 2:45 pm
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
May 9, 2013 at 4:13 am
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>
May 9, 2013 at 4:25 am
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.
May 9, 2013 at 9:51 am
: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 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