SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


convert datetime to integer


convert datetime to integer

Author
Message
DBA-640728
DBA-640728
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1127 Visits: 1995
sorry i edited

29
29
62
677
677
Bruce W Cassidy
Bruce W Cassidy
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1269 Visits: 1033
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.

Bruce W Cassidy
Bruce W Cassidy
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1269 Visits: 1033
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.

WayneS
WayneS
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9723 Visits: 10568
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
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

Bruce W Cassidy
Bruce W Cassidy
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1269 Visits: 1033
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.

DBA-640728
DBA-640728
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1127 Visits: 1995
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?
Andrew Diniz
Andrew Diniz
Old Hand
Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)

Group: General Forum Members
Points: 352 Visits: 293
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>Wink 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)
mt.bike.it
mt.bike.it
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 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.
john.arnott
john.arnott
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1958 Visits: 3059
@mt.bike.it,

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search