Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Date format in Millisecond.

Posted on 2006-03-30
7
Medium Priority
?
992 Views
Last Modified: 2011-09-20
GETDATE() in MS SQL return the standard format but I am interested in the number of millisecond from 1970 until now.   In MySQL there is the function UNIX_TIMESTAMP() that does this.  Is there a familiar functiontion to do this? i know it is simple but I cant seem to find it.
0
Comment
Question by:fylix0000
  • 3
  • 2
  • 2
7 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 16336539
Hi fylix0000,

The following works:

DECLARE @mili bigint
SET @mili=DATEDIFF(s, '1 Jan 1970', GETDATE())
SET @mili = @mili * 1000
SELECT @mili

It appears that trying to do it all in one expression will bomb because DATEDIFF returns type int, and that data type is
not big enough to hold all the miliseconds that have transpired since then...

Regards,

Patrick
0
 

Author Comment

by:fylix0000
ID: 16336582
Hmm, I was trying to use this function within the SQL stament.  SO i was looking for either a single function or some sort of DateFormat ( msec, GETDate())
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 16336613
fylix0000,

What is the SQL going to look like?

Regards,

Patrick
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:fylix0000
ID: 16336708
CREATE TEMPORARY TABLE acctmp
SELECT users.user_name, 0, UNIX_TIMESTAMP() * 1000
 FROM users LEFT JOIN accountStatus
ON users.user_name=accountStatus.user_name
WHERE status IS NULL;


INSERT INTO accountStatus SELECT * from acctmp;



Basically that is the original statement in MySQL, I am moving over to MS SQL 2000 and I am trying to convert the statement so it can work with MS SQL.  For some reason MS SQL does not like couple of things,

-UNIX_TIMESTAMP()
-TEMPORARY
-LEFT JOIN

I'm quite new to MS SQL so I am quite puzzle, is there a reference to MS SQL query language specifically?
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 16336724
I think you will have to do this:


DECLARE @ms BIGINT
SET @ms = (CAST(DATEDIFF(SECOND, '19700101', GETDATE()) AS BIGINT) * 1000) + DATEDIFF(MS, CONVERT(CHAR(8), GETDATE(), 112), GETDATE())
SELECT @ms
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 16336731
D'OH, sorry, forgot to put it into a function:


CREATE FUNCTION GetUnixTimestamp (@date DATETIME)
RETURNS BIGINT
AS
BEGIN
RETURN (CAST(DATEDIFF(SECOND, '19700101', @date) AS BIGINT) * 1000) + DATEDIFF(MS, CONVERT(CHAR(8), @date, 112), @date)
END --FUNCTION

0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 16336796
CORRECTION:

CREATE FUNCTION GetUnixTimestamp (@date DATETIME)
RETURNS BIGINT
AS
BEGIN
RETURN (CAST(DATEDIFF(SECOND, '19700101', @date) AS BIGINT) * 1000) + DATEPART(MS, @date)
END --FUNCTION
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question