How to INSERT INTO a temp table - mitr

Hi All,

First, I need to convert the data that's in sql datetime format to unix
timestamp format * 1000 then INSERT the unix

timestamp values INTO a temp table for later use.

After sometime searching, I found how to convert the sql datetime to unix
timestamp but having trouble using it to

populate the temp table.

Below is the syntax for converting datetime to unix timestamp and please
note that Query Analyzer kept giving me an

overflow error when I tried to run the query without the WHERE clause. The
SQL error: Difference of two datetime

columns caused overflow at runtime.

DECLARE @dt BIGINT
SET @dt= ''
SELECT @dt = (DATEDIFF(s, '19700101', dReviewed))
FROM review
WHERE nID = 1
SET @dt = @dt * 1000 -- in milliseconds
SELECT @dt

And below is the table definitioin and some sample data.

create table review ( nID int ,
nMessageID varchar (2000),
nReview smallint,
dReviewed datetime
)

insert into review
('1','<6F85BBEF5F62604E89F1183DEC2C56EF0D055D@jc-file-ktng01.justconnect.net>','2','2005-12-10

16:38:06')

insert into review
('2','<6F85BBEF5F62604E89F1183DEC2C56EF0D055D@jc-file-ktng01.justconnect.net>','2','2006-10-11

14:11:20')

insert into review
('3','<6F85BBEF5F62604E89F1183DEC2C56EF0D055D@jc-file-ktng01.justconnect.net>','2','2007-10-11

10:15:30')


The value for the first dReviewed in unix format is: 1134232686000

Thank you for the help!

--
Mitra
--
Mitra

How to INSERT INTO a temp table - Tom Cooper

You could get the time difference in seconds, convert it to a big int, then
multiply by 1000 and store that value in your table, for example

create table #review ( nID int ,
nMessageID varchar (2000),
nReview smallint,
dReviewed datetime
)
insert into #review Values
('1','<6F85BBEF5F62604E89F1183DEC2C56EF0D055D@jc-file-ktng01.justconnect.net>','2','2005-12-10
16:38:06')
insert into #review Values
('2','<6F85BBEF5F62604E89F1183DEC2C56EF0D055D@jc-file-ktng01.justconnect.net>','2','2006-10-11
14:11:20')
insert into #review  Values
('3','<6F85BBEF5F62604E89F1183DEC2C56EF0D055D@jc-file-ktng01.justconnect.net>','2','2007-10-11
10:15:30')
Select nID,
1000 * Cast(DATEDIFF(s, '19700101', dReviewed) As bigint) As UnixTimeStamp
Into #TempTable
From #review
Select * From #TempTable
Drop Table #TempTable
Drop Table #review

With your data that would return correct results,

nID         UnixTimeStamp
----------- --------------------
1           1134232686000
2           1160575880000
3           1192097730000

However, that introduces a problem if you have any dates in 2038 or beyond.
That is because DateDiff returns an integer, so the largest value that can
be returned is 2,147,483,647.  And 2,147,483,647 seconds past midnight on
1/1/1970 occurs in 2038. So if we add a new row,

insert into #review  Values
('4','<6F85BBEF5F62604E89F1183DEC2C56EF0D055D@jc-file-ktng01.justconnect.net>','2','2040-10-11
10:15:30')

then the above code will fail with the "Difference of two datetime columns
caused overflow at runtime." you were getting.  So, we need to use a time
value that won't cause an overflow no matter what date we are using, for
example, days.  Then cast it as a bigint, then multiply by the number of
milliseconds in a day (24*60*60*1000).  That will give us the number of
milliseconds from 1/1/1970 to midnight of the day we are attempting to
convert.  Then add the number of milliseconds from midnight to the actual
time we are trying to convert.  So

Select nID,
24*60*60*1000 * Cast(DATEDIFF(d, '19700101', dReviewed) As bigint)
+ DateDiff(ms, DateAdd(d, 0, DateDiff(d, 0, dReviewed)), dReviewed) As
UnixTimeStamp
Into #TempTable
From #review
Select * From #TempTable

gives a bullet proof result that should work with any valid datetime value.

Tom

How to INSERT INTO a temp table - mitr

Hi Tom,

Thank you for responding to my question. The SELECT INTO works as long as
the number of rows don't exceed 1,410.  When I try to convert and store all
rows, over 10,000s, in the temp table I get this error:
Server: Msg 535, Level 16, State 1, Line 1
Difference of two datetime columns caused overflow at runtime.

I am not familiar with CURSORS but do you suggest using CURSORS to select
into the temp table?

Again, thank you for your help,

--
Mitra

How to INSERT INTO a temp table - Tom Cooper

Are you getting this error with the second version of the query (the one
that does the difference in days)?

If you run the following query, what is the result?
SELECT MIN(dReviewed), MAX(dReviewed) FROM review

A cursor is not the solution to this problem.  Generally, cursors are slow
and almost always a bad choice.  There are a very few cases where cursors
are the best solution, but this is not one of them.

Tom

How to INSERT INTO a temp table - mitr

I was getting the error with the first version of the query. From your
comment that I need to use the second query only if I have dates beyond 2038,
I figured the first query should work since I don't have any dates beyond
2007. But now that you are questioning with which query I got the error, I
figured I should go ahead and try the second version of the query. It worked
perfect!

Thank you!
--
Mitra

How to INSERT INTO a temp table - mitr

Tom,

I have an issue with the SELECT INTO query statement that you suggested.

Not all rows in my Review table have a datetime value in the dReviewed
column. Both versions of the query will set UnixTimeStamp = -2208988800000
when there is no data in the dReviewed column . And when I add a WHERE clause
to the query, then I only get the rows that do have data in the dReviewed
column.

How can I insert all the rows from the Reviewed table into the
tempReviewTable, including the ones that don't have any value in the
dReviewed column without inserting a negative number into the UnixTimeStamp
column?

Thank you!
--
Mitra

How to INSERT INTO a temp table - Tom Cooper

The only way to have a datetime column with no value is to set it to NULL.
Note that NULL is not the empty string '' and it is not spaces '  '.  But
the rows which are causing you this problem don't have NULLs in them since
my query would return NULL for those rows.  What I am sure that has happened
is that you tried to put an empty string '' or a string consisting of one or
more spaces into dtReviewed.  When you do this, SQL Server sets the day to
the default date, which is Jan 1, 1900 and the time to the default time
which is midnight.  So my second query will return the time in these rows
(1900/01/01 00:00:00.000) to the number of milliseconds that time is before
1/1/1970 which is -2208988800000.  The reason my first query died is the the
real problem is that when it converts the dates to milliseconds the date
must be within about 68+ years of 1/1/1970 either 68+ years after 1/1/1970
(sometime in 2038) or 68+ years before 1/1/1970 (sometime in 1901).

I'm not sure what you want put into that bigint column in these cases.  You
say you don't want a negative number, in that case, you could try something
like

Select nID,
Case
When dReviewed >= '19700101'
Then 24*60*60*1000 * Cast(DATEDIFF(d, '19700101', dReviewed) As bigint)
+ DateDiff(ms, DateAdd(d, 0, DateDiff(d, 0, dReviewed)), dReviewed)
Else NULL
End
As UnixTimeStamp
Into #TempTable
From #review

which will put the number of milliseconds since 1/1/1970 if the date is on
or after 1/1/1970, otherwise it puts NULL in that column.

Tom