convert datetime to integer

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » convert datetime to integer

19 posts, Page 2 of 2

««1 2

convert datetime to integer

Rate Topic

Display Mode

Topic Options

Author

Message

DBA-640728

Posted Tuesday, May 05, 2009 3:38 PM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 5:21 PM
Points: 496, Visits: 1,724

sorry i edited

29
29
62
677
677

Post #710701

Bruce W Cassidy

Posted Tuesday, May 05, 2009 3:43 PM

SSC Eights!

Group: General Forum Members
Last Login: Friday, March 22, 2013 11:49 AM
Points: 945, Visits: 998

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.

Post #710703

Bruce W Cassidy

Posted Tuesday, May 05, 2009 3:56 PM

SSC Eights!

Group: General Forum Members
Last Login: Friday, March 22, 2013 11:49 AM
Points: 945, Visits: 998

DBA (5/5/2009)

sorry i edited

29
29
62
677
677

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.

Post #710710

WayneS

Posted Tuesday, May 05, 2009 4:40 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:50 AM
Points: 6,367, Visits: 8,227

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

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Post #710727

Bruce W Cassidy

Posted Tuesday, May 05, 2009 4:53 PM

SSC Eights!

Group: General Forum Members
Last Login: Friday, March 22, 2013 11:49 AM
Points: 945, Visits: 998

WayneS (5/5/2009)

As you can see, just converting the field to an int is giving you the desired results.

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.

Post #710732

DBA-640728

Posted Wednesday, May 06, 2009 8:48 AM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 5:21 PM
Points: 496, Visits: 1,724

thank you so much!!! one last question, i did this in my testing environment and worked perfectly; however in my production env i have replication, would this represent a problem to my subscriber?

Post #711159

Andrew Diniz

Posted Thursday, March 08, 2012 4:47 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 3:04 AM
Points: 189, Visits: 243

DBA-640728 (5/6/2009)

thank you so much!!! one last question, i did this in my testing environment and worked perfectly; however in my production env i have replication, would this represent a problem to my subscriber?

Careful! This doesn't hold true when GETDATE() returns a PM time. CONVERT(int, <datetime>) rounds to the nearest int - not truncates.
Try it out:

DECLARE @pm_date datetime;
SET @pm_date = '20120131 12:00:01';

SELECT
DATEDIFF(DAY, '19000101', @pm_date),
CAST(@pm_date AS int)

Post #1263539

mt.bike.it

Posted Wednesday, March 28, 2012 1:53 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, March 28, 2012 2:16 PM
Points: 1, Visits: 1

I am in search of this as well... over 5000 records all in date format. It is indeed a date yyyymmdd.
Problem is that whoever created the table in the database that I need to import into has the type as a decimal Wink

So - I just want to change my column so I can import it and yet Excel is outsmarting me - wanting to give me the number equivalent. I have to keep the same "date" values.

I tried copying the values into a new column = general, special, number equivalent Smile

Insanity to outsmart what it thinks I want lol - 20110219 turns into 40593

I understand why it converts - I need to outsmart Excel for my purposes.

Post #1274628

john.arnott

Posted Wednesday, March 28, 2012 3:56 PM

UDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 10:57 PM
Points: 1,491, Visits: 3,008

@mt.bike.it,

You'll want to use the "Paste Special" menu option in Excel to "paste values" rather than contents.

Post #1274733

« Prev Topic | Next Topic »

19 posts, Page 2 of 2

««1 2

Permissions