May 5, 2009 at 7:42 am
hello what would be the easiest way to convert a datetime column into an integer column?
May 5, 2009 at 8:06 am
I wouldn't advise this kind of operation to a less optimal data type.
You lose all datatime functions and validations !!
Chose your level of detail !
declare @dt datetime
Select @dt = GETDATE()
Select CONVERT(varchar(26),@dt, 121)
, CONVERT(bigint, replace(replace(replace(replace(convert(varchar(23),@dt,121),'-',''),':',''),'.',''),' ',''))
, CONVERT(int, @dt) -- date offset starting 1899-12-31
, DATEADD( D, -(CONVERT(int, @dt) ), @dt)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 5, 2009 at 8:17 am
Why would you like to do that!
May 5, 2009 at 10:04 am
Datetime data types are internally a float. The whole number portion represents the days, while the decimal portion represents the time.
ie:
select CONVERT(float, CONVERT(datetime, '99991231 23:59:59.997')), CONVERT(float, GetDate())
If you change the datetime to integer, you will lose all time portions of those dates.
If you're on 2008, you might want to look instead at the Date data type.
Why would you want to change to an integer?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 5, 2009 at 1:34 pm
WayneS, has a nice description of how dates are stored internally. But, what do you want the INT to represent? Do you want a number like YYYYMMDD or the integer portion of SQL's internal date or ???SELECT CAST(CONVERT(VARCHAR(8), CURRENT_TIMESTAMP, 112) AS INT)
SELECT CAST(CURRENT_TIMESTAMP AS INT)
-- etc..
May 5, 2009 at 2:18 pm
when creating a table we made a mistake it was supposed to be an integer column and is supposed to store by default a distributer ID using a function. (ftDistID:int,null)
May 5, 2009 at 2:28 pm
What sort of values are in that column now? Can you please post some sample values as they show up in date format and the desired integer value for each?
May 5, 2009 at 3:11 pm
The datetime data type is stored internally as two 4-byte integers concatenated together rather than a true floating point type.
The first 4-byte integer represents the date portion of the datetime value. The numeric value of this integer represents the number of days since the reference date 0 (1900-01-01). The valid numeric range of this integer is -53690 (1753-01-01) to 2958463 (9999-12-31).
The second 4-byte integer represents the time portion of the datetime value. The numeric value of this integer represents the number of 1/300 second intervals since midnight. The valid numeric range of this integer is 0 (00:00:00.000) to 25919999 (23:59:59.997).
There are numerous ways of converting a datetime to an integer value, and the OP hasn't stated which method is required. These methods fall into 2 categories
1) Where the integer represents the number of time intervals since some reference datetime, e.g.
DECLARE @dt datetime
SELECT @dt = GETDATE()
/* Number of days since 1900-01-01 */
SELECT DATEDIFF(day, 0, @dt)
/* Number of 1/300 second intervals since midnight (ignoring date component of datetime) */
SELECT CONVERT(int, CONVERT(binary(4), @dt))
/* Number of days since the release of the first Velvet Underground studio album (1967-03-12) */
SELECT DATEDIFF(day, 24541, @dt)
2) Where the integer is a numeric representation of a human-readable date or time format, e.g.
/* YYYYMMDD format */
SELECT (CAST(YEAR(@dt) AS bigint) * 100 + MONTH(@dt)) * 100 + DAY(@dt)
May 5, 2009 at 3:15 pm
sure
now:
1900-01-30 00:00:00.000
1900-01-30 00:00:00.000
1900-03-04 00:00:00.000
1901-11-09 00:00:00.000
1901-11-09 00:00:00.000
desired :
29
29
62
677
677
the data type for the column should had been
udDistID(int,not null)
can you help?
May 5, 2009 at 3:21 pm
DBA (5/5/2009)
surenow:
1900-01-30 00:00:00.000
1900-01-30 00:00:00.000
1900-03-04 00:00:00.000
1901-11-09 00:00:00.000
1901-11-09 00:00:00.000
desired :
1452
525
2
1525
1452
the data type for the column should had been
udDistID(int,not null)
can you help?
Only problem I see based on the above is that the int values are consistant with the dates provided.
May 5, 2009 at 3:38 pm
sorry i edited
29
29
62
677
677
May 5, 2009 at 3:43 pm
[font="Verdana"]What is in your "udDistID(int,not null)"?
it sounds to me you are looking to replace a datetime column with an integer column. As a suggestion, try the following:
1. Rename the datetime column
2. Add a new column with the correct name and type (int not null, default 0).
3. Run a query to update the new column with the correct value.
If necessary, you can then drop the datetime column.
[/font]
May 5, 2009 at 3:56 pm
DBA (5/5/2009)
sorry i edited29
29
62
677
677
[font="Verdana"]That looks like the number of days from 1900-01-01. So your conversion will be something like:
datediff(day, '19000101', MyDate)
...where MyDate is the name of the column containing the datetime value.
[/font]
May 5, 2009 at 4:40 pm
DBA (5/5/2009)
when creating a table we made a mistake it was supposed to be an integer column and is supposed to store by default a distributer ID using a function. (ftDistID:int,null)
Okay, now this makes sense as to why you want to do it.
Using the sample code you provided, I created this test:
if object_id('tempdb..#Dates') is not null DROP TABLE #Dates
CREATE TABLE #Dates (DateField datetime)
insert into #Dates
select '1900-01-30 00:00:00.000' union all
select '1900-01-30 00:00:00.000' union all
select '1900-03-04 00:00:00.000' union all
select '1901-11-09 00:00:00.000' union all
select '1901-11-09 00:00:00.000'
select DateField, convert(int, DateField) from #Dates
As you can see, just converting the field to an int is giving you the desired results.
Based on this, you would think that you could just do this:
ALTER TABLE "tablename" ALTER COLUMN "columnname" INTEGER
if you try this, you will get this error:
Msg 257, Level 16, State 3, Line 1
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.
So, you will have to add a new field to your table, populate it with the integer representation of your date field, and finally dropping the date field. My final code for showing all of this is:
if object_id('tempdb..#Dates') is not null DROP TABLE #Dates
CREATE TABLE #Dates (DateField datetime)
insert into #Dates
select '1900-01-30 00:00:00.000' union all
select '1900-01-30 00:00:00.000' union all
select '1900-03-04 00:00:00.000' union all
select '1901-11-09 00:00:00.000' union all
select '1901-11-09 00:00:00.000'
select DateField, convert(int, DateField) from #Dates
GO
alter table #Dates add NewDateField INT
GO
update #Dates set NewDateField = convert(int, DateField)
select * from #Dates
GO
alter table #Dates DROP COLUMN DateField
GO
select * from #Dates
Of course, MAKE SURE that you try this out IN A TEST ENVIRONMENT. As "the keeper of the data", your first priority is retaining that accurate data that you do have. Only do this in production when you are sure you will get what you want.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 5, 2009 at 4:53 pm
WayneS (5/5/2009)
As you can see, just converting the field to an int is giving you the desired results.
[font="Verdana"]Heh. I learn something every day. These two statements are equivalent.
select
datediff(day, '19000101', getdate()),
cast(getdate() as int);
That makes sense when I think about it. I'm not so sure it would work for the new date datatypes in SQL Server 2008 though.
[/font]
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply