Solved

Convert datetime to int

Posted on 2003-12-09
18
8,473 Views
Last Modified: 2012-05-04
How do I convert a field with the type of datetime to int ?
I need to convert the data it contains too.

 Thanks !
0
Comment
Question by:kukiya
  • 7
  • 7
  • 3
  • +1
18 Comments
 
LVL 18

Expert Comment

by:ShogunWade
ID: 9904577
If you use enterprise manager it will do all the graft for you.
0
 

Author Comment

by:kukiya
ID: 9904744
I need an SQL query to do so...
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 9904784
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

0
 

Author Comment

by:kukiya
ID: 9904897
I run you script and the following error occured :
Line 4: Incorrect syntax near '.'.

This is becuase of INT.MyColumn I guess...
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 9904966
sorry  CONVERT(INT.MyColumn)  should be  CONVERT(INT,MyColumn)
0
 

Author Comment

by:kukiya
ID: 9905004
ok, Thanks.
Now.. another error occures:

"Insert Error: Column name or number of supplied values does not match table definition."
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 9905025
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
0
 
LVL 26

Assisted Solution

by:Hilaire
Hilaire earned 25 total points
ID: 9905058
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
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 9905102
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:
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 9905135
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:


0
 

Author Comment

by:kukiya
ID: 9905214
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.
0
 

Author Comment

by:kukiya
ID: 9905369
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 !!!
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 9905396
If you want the number of days from a given point then you need to use DATEDIFF(day,@FixedDate,MyField)
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 25 total points
ID: 9905424
?  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?
0
 

Author Comment

by:kukiya
ID: 9910408
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
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 9911051
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?
0
 

Author Comment

by:kukiya
ID: 9911119
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.
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 9911647
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.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

862 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now