datetime to unix epoch time

Is there a function in SQL Server to convert a datetime into Unix Epoch time,
that is seconds elapsed since Jan 1 1970. The result will be an big integer.
Thanks.
Some sample:
Select datediff(ss,'19700101',Getdate())
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Data Cruncher" <dcruncher4@.netscape.net> schrieb im Newsbeitrag
news:3got4gFdjc93U1@.individual.net...
> Is there a function in SQL Server to convert a datetime into Unix Epoch
> time,
> that is seconds elapsed since Jan 1 1970. The result will be an big
> integer.
> Thanks.
>
|||And if you want a BIgint then
Select Convert(Bigint,datediff(ss,'19700101',Getdate()))
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:u7DRv0FbFHA.2968@.TK2MSFTNGP10.phx.gbl...
> Some sample:
> Select datediff(ss,'19700101',Getdate())
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Data Cruncher" <dcruncher4@.netscape.net> schrieb im Newsbeitrag
> news:3got4gFdjc93U1@.individual.net...
>

datetime to unix epoch time

Is there a function in SQL Server to convert a datetime into Unix Epoch time
,
that is seconds elapsed since Jan 1 1970. The result will be an big integer.
Thanks.Some sample:
Select datediff(ss,'19700101',Getdate())
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Data Cruncher" <dcruncher4@.netscape.net> schrieb im Newsbeitrag
news:3got4gFdjc93U1@.individual.net...
> Is there a function in SQL Server to convert a datetime into Unix Epoch
> time,
> that is seconds elapsed since Jan 1 1970. The result will be an big
> integer.
> Thanks.
>|||And if you want a BIgint then
Select Convert(Bigint,datediff(ss,'19700101',Ge
tdate()))
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:u7DRv0FbFHA.2968@.TK2MSFTNGP10.phx.gbl...
> Some sample:
> Select datediff(ss,'19700101',Getdate())
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Data Cruncher" <dcruncher4@.netscape.net> schrieb im Newsbeitrag
> news:3got4gFdjc93U1@.individual.net...
>

datetime to unix epoch time

Is there a function in SQL Server to convert a datetime into Unix Epoch time,
that is seconds elapsed since Jan 1 1970. The result will be an big integer.
Thanks.Some sample:
Select datediff(ss,'19700101',Getdate())
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Data Cruncher" <dcruncher4@.netscape.net> schrieb im Newsbeitrag
news:3got4gFdjc93U1@.individual.net...
> Is there a function in SQL Server to convert a datetime into Unix Epoch
> time,
> that is seconds elapsed since Jan 1 1970. The result will be an big
> integer.
> Thanks.
>|||And if you want a BIgint then
Select Convert(Bigint,datediff(ss,'19700101',Getdate()))
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:u7DRv0FbFHA.2968@.TK2MSFTNGP10.phx.gbl...
> Some sample:
> Select datediff(ss,'19700101',Getdate())
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Data Cruncher" <dcruncher4@.netscape.net> schrieb im Newsbeitrag
> news:3got4gFdjc93U1@.individual.net...
>> Is there a function in SQL Server to convert a datetime into Unix Epoch
>> time,
>> that is seconds elapsed since Jan 1 1970. The result will be an big
>> integer.
>> Thanks.
>sql

DateTime Issue

How do I exclude showing the seconds out of a datetime column?
I tried converting it to smalldatetime but it only rounds it up to "00".

e.g.

2004-01-03 16:33:20

I want to show only:
2004-01-03 16:33

I know this can be achive by using Datepart function calling each
part of the datetime, but is there a simplier way?

Basically the datetime value will be shown to a ASP page. And I don' t want to show until seconds.Have you tried to cast/convert it to a varchar ?|||ooh...thanks...,
err...what if I want it to remain showing numbers instead of the date names?
Cast/convert will change the datetime column into date names
e.g. JAN,FEB...etc....|||Like ...

convert(varchar(16),datecol,121)|||Thanks..that was a fast response.|||When you need to change the format of the date - look in bol under "Cast and Convert", it will show you the formats that enigma references.

DateTime comparison with some exceptions

I have StratDateTime and EndDateTime fields in the table. I need to compare this two datetime fields and find seconds. I can use DateDiff but there are the following exceptions:

1. Exclude seconds coming from the date which are Saturday and Sunday

2. Exclude seconds coming from time range between 7:01pm and 6:59am

3. Exclude seconds coming from Jan 1^st and Jul 4^th.

So do you want to make the difference between the two columns in seconds a column in the query results or do you want to compare them to each other or some other values in the WHERE clause of the query? I'd suggest that you post the query you have written and then one of us can help you with the query, as it is you have not really posted enough information for us to help you.|||

Ok. Thank you very much for your response.

SELECT Datediff(ss,StartDateTime,EndDateTime) AS mySeconds

FROM MyTable

This will return seconds. However this does not hold all the exceptions I listed above. Let’s say I have StartDateTime=12/15/2006 7:00pm and EndDateTime=12/18/2006 9:00am, then the difference should be 2 hours because between 12/15/2006 7:00pm and 12/18/2006 7:00am is not a business period, the rest is 2 business hours.

|||This is the same question asked by you before

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1022431&SiteID=1

I will again suggest you to use the calender-table. This will make life easy as you are not able to know 12/16/2006, is Saturday or working day.
With calender-table you can easily find that, more over you can create the holiday list too, find the difference between any date & more functionality can be add according to your own requirements.

Gurpreet S. Gill|||

Thank you very much for your help. That does not work for me since it is considering the day, not the time. My business day should be between 7:00am and 7:00pm in the weekdays. I do not see how getting number of business days would really help.

I would ask the same question, let’s say I have a calendar table, how would I get calendar table return me 2 hours for the following example. I have StartDateTime=12/15/2006 7:00pm and EndDateTime=12/18/2006 9:00am, then the difference should be 2 hours because between 12/15/2006 7:00pm and 12/18/2006 7:00am is not a business period, the rest is 2 business hours.

Thanks you very much for your help.

|||

What you need to do is create a user defined function to calculate your desired value. It will look like this, I haven't put in all the conditional code for you, that will take a while, but you get the idea.

CREATE FUNCTION BusinessSeconds(@.StartTime datetime, @.EndTime datetime)
RETURNS int
AS
BEGIN
DECLARE @.retVal int
SET @.retVal = datediff(ss, @.StartTime, @.EndTime)
--Conditional code here to subtract your non-business periods
--eg. IF ... SET @.retVal = @.retVal - 86400
RETURN @.retVal
END

And you'll use it like this

SELECT dbo.BusinessSeconds(StartDateTime, EndDateTime) AS MySeconds
FROM MyTable

Datepart help

Hi
How can I trim the seconds off the following datetime example?
6/10/2002 15:57:44 PM
I need to put this in my where clause:
Update stage_fact_account
set fire_fee_balance = whjointdata.dbo.stage_fire_fee.stage_fire_fee_bal
from whjointdata.dbo.stage_fire_fee
where whjointdata.dbo.stage_fire_fee.policy_number =
stage_fact_account.policy_number
and whjointdata.dbo.stage_fire_fee.policy_date_time =
stage_fact_account.account_date_time
and whjointdata.dbo.stage_fire_fee.portfolio_set =
stage_fact_account.portfolio_set
Thanks!Hi Patrice,
Select DATEPART(ss,'6/10/2002 15:57:44 PM')
HTH, jens Suessmeyer.|||sorry, i should have been more precise - I just want the month, day, year,
and minutes, but not the seconds....
"Jens" wrote:

> Hi Patrice,
> Select DATEPART(ss,'6/10/2002 15:57:44 PM')
>
> HTH, jens Suessmeyer.
>|||In that case, use CONVERT (CHAR(16), YourDateColumn, 120) to make it into a
string that truncates the seconds.
RLF
"Patrice" <Patrice@.discussions.microsoft.com> wrote in message
news:C7218353-905B-4505-8AAF-454C50DFA02A@.microsoft.com...
> sorry, i should have been more precise - I just want the month, day, year,
> and minutes, but not the seconds....
> "Jens" wrote:
>|||On Tue, 8 Nov 2005 06:46:13 -0800, Patrice wrote:

>sorry, i should have been more precise - I just want the month, day, year,
>and minutes, but not the seconds....
Hi Patrice,
In addition to Russell's suggestion, you could also use:
SELECT CAST(YourDate AS smalldatetime)
(truncates the seconds)
or
SELECT DATEADD(ss, -DATEPART(ss, YourDate), YourDate)
(rounds to the nearest minute)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

DateDiff Trivial Problem

Hy Friends,
I'm having a new annoying problem wiht T-SQL.
I need to remove 10 Seconds from a Datetime Value.
Somethin like This:
This date
07/24/2003 14:25:02
I want it like this
07/24/2003 14:24:52
Suggestions?select DATEADD(ss, -10, date_column) from table

Dateadd - Time accumulation

Hi.
I'm in the process of converting seconds into an HH:mm:ss format using the
following statement...
=DateAdd("s", Sum(Fields!TimeInSeconds.Value), #01/01/0001#)
This gives me an absolute value of the beginning of time, which is great.
The problem arises when I clock over the 24h scenario, and my output with
the format of HH:mm:ss just displays as 01:00:00 (if 25 hours have
accumulated).
The base value of my field would now show as #01/02/001 01:00:00#.
I would like to see this as 25:00:00
Any ideas.
Thanks
GaryYou may want to look at the DateDiff VB.NET function.
E.g. =Datediff("s", Fields!End_Time.Value, Fields!Start_Time.Value)
Details on MSDN:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/vsfctdatediff.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:2E179571-45B0-4C7B-A316-FBA29E8F53D7@.microsoft.com...
> Hi.
> I'm in the process of converting seconds into an HH:mm:ss format using the
> following statement...
> =DateAdd("s", Sum(Fields!TimeInSeconds.Value), #01/01/0001#)
> This gives me an absolute value of the beginning of time, which is great.
> The problem arises when I clock over the 24h scenario, and my output with
> the format of HH:mm:ss just displays as 01:00:00 (if 25 hours have
> accumulated).
> The base value of my field would now show as #01/02/001 01:00:00#.
> I would like to see this as 25:00:00
> Any ideas.
> Thanks
> Gary