We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Date format in Millisecond.

fylix0000
fylix0000 asked
on
Medium Priority
1,012 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.
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2010

Commented:
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

Author

Commented:
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())
CERTIFIED EXPERT
Top Expert 2010

Commented:
fylix0000,

What is the SQL going to look like?

Regards,

Patrick

Author

Commented:
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?
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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

Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
CORRECTION:

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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.