UDF to Convert UNIX time into 12 hour time

Hmm, that's interesting.  Are you sure about the conversion of that value :-) ?  The UNIX dates I've seen are seconds displacement from 01/01/1970.  This one is clearly not.  

In fact, based on those values, the base date appears to be '1972-03-02 13:51:36.000'.  So, the function would be something like this:


CREATE FUNCTION convUnixDate (
      @unixDate INT
)
RETURNS VARCHAR(24)
AS
BEGIN
RETURN DATEADD(SECOND, @unixDate, '1972-03-02 13:51:36.000')
END --FUNCTION

MikeWalsh
>>I assume you know that piece of information?

If I knew the conversion I could come closer that I have at this solution and would have lowered the points :)

I have scoured the web and have come up very short. I just have the date (the numbers) from another system and I need to handle it. I read somewhere (sry can't remember where) that the number represents the number of seconds after a certain date (Jan 1, 1600?? or somethign like that)...

ScottPletcher
>>Are you sure about the conversion of that value :-)

You are correct. The date I gave in the sample is not correct what-so-ever, I just put it there to show what I needed. Your fucntion looks amazingly simple and good. I just need to verify that the conversion is working.

OK, then most likely just change the base date in the function to '1970-01-01 00:00:00' and it should work.

BTW
>>(sry can't remember where)

Here is where:

http://www.aspfaq.com/show.asp?id=2451

ScottPletcher

You stud! I put the output of the aspfaq function into your function (with the new date) and it tested out perfect, however how about 12 hour format rather then 24?

I didn't realize that the date you gave was a sample, hence my question. You never know with some of the questions and the question askers out there why someone gives the points value they give.. But it looks like Scott's solution is just what you needed.

DOH!  Forgot about that part!

If you can accept the date as 'Mon dd yyyy', like 'Jun 29 2003', you can do this

RETURN CONVERT(VARCHAR(24), DATEADD(SECOND, @unixDate, '1970-01-01 00:00'), 100)


If, as is likely, you prefer mm/dd/[yy]yy hh:mmAM', then you can do this:


RETURN CONVERT(CHAR(11), DATEADD(SECOND, @unixDate, '1970-01-01 00:00'), 101) + SUBSTRING(CONVERT(CHAR(20), DATEADD(SECOND, @unixDate, '1970-01-01 00:00'), 100), 13, 7)

-- if you want only yy (03), not yyyy (2003), then change CHAR(11) to CHAR(9) and change ", 101" to ", 1".

Very cool. Just one more curious thing...why is there 2 spaces inbetween the date and time? (Jun 20 2003  2:41PM)

Because there has to be room another digit in from of the ":" for 10/11/12.  Basically instead of "02", SQL is putting "2", but if it were, for example, "10:41PM", it would have to include the first digit of the hour and thus needs the extra space :-).  That also allows multiple times will line up correctly, such as:


Jun 20 2003   2:41PM
Jun 20 2003 12:41PM
Jun 21 2003   1:17AM
Jun 21 2003 11:23AM

etc.

Thanks for your help on this one.

fyi -
SELECT dbo.convUnixDate(8132287790)

returns:
Arithmetic overflow error converting expression to data type int.

That's true.  And that could be a problem if you need to work with dates beyond 2038-01-19, for example, with insurance policies, etc..  Honestly, I'm not sure how to get around it, since, from what I can tell, DATEADD takes only an INT parameter (not BIGINT).  I think MS needs to enhance this.