Convert datetime to int

How do I convert a field with the type of datetime to int ?
I need to convert the data it contains too.

 Thanks !
kukiyaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ShogunWadeCommented:
If you use enterprise manager it will do all the graft for you.
kukiyaAuthor Commented:
I need an SQL query to do so...
ShogunWadeCommented:
OK well given that ALTERing columns in tables with the ALTER statement requires that the datatype can be impolicitly cast from old to new  the only way to do this is to select all your data into a temporary table

truncate the original table then alter the table and select it bacck in again.

This is infact exactly what enterprise manager does.


So.....
assuming a 1 column table for simplicity,

SELECT * INTO ##TempTable FROM MyTable
TRUNCATE  TABLE MyTable
ALTER TABLE MyTable ALTER COLUMN MyColumn INT
INSERT MyTable SELECT CONVERT(INT.MyColumn) FROM MyTable

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

kukiyaAuthor Commented:
I run you script and the following error occured :
Line 4: Incorrect syntax near '.'.

This is becuase of INT.MyColumn I guess...
ShogunWadeCommented:
sorry  CONVERT(INT.MyColumn)  should be  CONVERT(INT,MyColumn)
kukiyaAuthor Commented:
ok, Thanks.
Now.. another error occures:

"Insert Error: Column name or number of supplied values does not match table definition."
ShogunWadeCommented:
INSERT MyTable SELECT CONVERT(INT.MyColumn) FROM MyTable

in this insert statement you will need to add all the original columns but substitute your columun you want to convert with the CONVERT bit.

EG:

for 2 columns:


INSERT MyTable SELECT myfirstcolumn,CONVERT(INT.MyColumn) FROM MyTable
HilaireCommented:
Hi kukiya

FYI this int value will be a number of days between the date and the 01/01/1900

as a consequence,
if several dates have different time parts but same date part,
they will be translated in the same int value

does this fit your needs ?

Hilaire
Scott PletcherSenior DBACommented:
Actually, you can do this different ways.

1) Add a new column to contain the converted value, set the new column to the conversion of the old column; drop the old column; rename the new column.  For example:

ALTER TABLE yourTable
ADD tempCol INT

UPDATE yourTable
SET tempCol = CAST(dateCol AS INT)
IF @@ERROR <> 0
    GOTO errMsg

ALTER TABLE yourTable
DROP COLUMN dateCol

EXEC sp_rename 'yourTable.tempCol', 'dateCol', 'COLUMN'

GOTO done

errMsg:
PRINT 'Error(s) during UPDATE, cannot complete processing'

done:
Scott PletcherSenior DBACommented:
2) If the table has a primary key column(s) [or column(s) that uniquely identify a row even if not explicitly a PK], then you can create another holding table with just the PK and the new values, then use that to UPDATE the original table.  For example:

BEGIN TRANSACTION

CREATE TABLE dbo.yourTableConversion (id INT, dateColConv INT)

INSERT INTO yourTableConversion
SELECT id, CAST(dateCol AS INT) AS dateColConv
FROM yourTable
IF @@ERROR <> 0
    GOTO errMsg

ALTER TABLE yourTable
DROP COLUMN dateCol

ALTER TABLE yourTable
ADD dateCol INT

UPDATE yourTable
SET dateCol = dateColConv
FROM yourTable
INNER JOIN yourTableConversion ytc ON yourtable.id = ytc.id

GOTO done

errMsg:
PRINT 'Error(s) during INSERT, cannot complete processing'

done:


kukiyaAuthor Commented:
ShogunWade,

I added the name of the fileld but still got an error:

Disallowed implicit conversion from data type datetime to data type int, table 'MyDb.dbo.MyTable', column 'MyColumn '. Use the CONVERT function to run this query.
kukiyaAuthor Commented:
ScottPletcher,

I tried your fist code. It does work great,
 but the value in the new int filed is: 37958, which is 1/1/1970.
The coversion does not give the correct value of the orginal field...

Thanks !!!
ShogunWadeCommented:
If you want the number of days from a given point then you need to use DATEDIFF(day,@FixedDate,MyField)
Scott PletcherSenior DBACommented:
?  It looks to me as if 37958 is Dec 5, 2003, based on a base date of 1900-01-01:

SELECT DATEADD(DAY, 37958, '1900-01-01')


Btw, is this a SMALLDATETIME or a DATETIME?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kukiyaAuthor Commented:
When I translate 37958 to CTime in my MFC code

CTime time(37958);
time.Format("'%m/%d/%Y %H:%M:%S'");

time will be: 1/1/1970 12:32:38
ShogunWadeCommented:
The reason for this is that different languages use a different offset.

I am confused as to why you would want to convert the date to an integer only to convert it back again in the client app, isnt this wasteful?
kukiyaAuthor Commented:
its just that I need to adapt a certain DB to an application.
The DB field should be int, and the application should present it as a date.
ShogunWadeCommented:
in that case you will need to decide your own fixed offest for the integer  and calculate dates using DATEADD in sql  or the equivelent in any other language used.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.