Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

convert datetime to integer Expand / Collapse
Author
Message
Posted Tuesday, May 5, 2009 3:38 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 8:26 AM
Points: 537, Visits: 1,923
sorry i edited

29
29
62
677
677
Post #710701
Posted Tuesday, May 5, 2009 3:43 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:12 PM
Points: 758, Visits: 1,031
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
Posted Tuesday, May 5, 2009 3:56 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:12 PM
Points: 758, Visits: 1,031
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
Posted Tuesday, May 5, 2009 4:40 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:14 PM
Points: 5,364, Visits: 8,952
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
Posted Tuesday, May 5, 2009 4:53 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:12 PM
Points: 758, Visits: 1,031
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
Posted Wednesday, May 6, 2009 8:48 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 8:26 AM
Points: 537, Visits: 1,923
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
Posted Thursday, March 8, 2012 4:47 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:11 AM
Points: 216, Visits: 273
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
Posted Wednesday, March 28, 2012 1:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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

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

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
Posted Wednesday, March 28, 2012 3:56 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,388, Visits: 3,039
@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 »

Add to briefcase ««12

Permissions Expand / Collapse