InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 17:03:04 GMT20http://www.sqlservercentral.com/Forums/Topic639222-8-1.aspx[quote][b]J (1/22/2009)[/b][hr]The OP has ignored repeated requests as to the WHY of it.On top of that he has been warned repeatedly that is a bad idea.If all he wants is a code drone to issue an SQL statement and closes the door to man-centuries of expertise in doing it the right way, I suggest we let this thread die right now.Let us not waste a second more of our time with this.[/quote]Heh... I wonder if the OP likes pork chops. :hehe:[font="Arial Black"][i]SLAM![/i][/font]Thu, 22 Jan 2009 18:54:32 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic639222-8-1.aspxThe OP has ignored repeated requests as to the WHY of it.On top of that he has been warned repeatedly that is a bad idea.If all he wants is a code drone to issue an SQL statement and closes the door to man-centuries of expertise in doing it the right way, I suggest we let this thread die right now.Let us not waste a second more of our time with this.Thu, 22 Jan 2009 12:14:08 GMTJ-440512http://www.sqlservercentral.com/Forums/Topic639222-8-1.aspx[quote][b]munishprathap (1/20/2009)[/b][hr]Thanks friends for all your supporti can achieve this task by the following Query,please let me know your feedbackIF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME = 'Note' AND COLUMN_NAME = 'LastModifyBy' AND DATA_TYPE = 'datetime' )BEGINALTER TABLE [dbo].[Note] ALTER COLUMN LastModifyBy VARCHARUPDATE [dbo].[Note] SET LastModifyBy='0'ALTER TABLE [dbo].[Note] ALTER COLUMN LastModifyBy BIGINTEND[/quote]Id addition to answering Jeff's question:What version of SQL Server are you using? - This is a SQL Server 7,2000 forum section.Have you tested the code?Wed, 21 Jan 2009 07:01:30 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic639222-8-1.aspx[quote][b]munishprathap (1/20/2009)[/b][hr]Thanks friends for all your supporti can achieve this task by the following Query,please let me know your feedbackIF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME = 'Note' AND COLUMN_NAME = 'LastModifyBy' AND DATA_TYPE = 'datetime' )BEGINALTER TABLE [dbo].[Note] ALTER COLUMN LastModifyBy VARCHARUPDATE [dbo].[Note] SET LastModifyBy='0'ALTER TABLE [dbo].[Note] ALTER COLUMN LastModifyBy BIGINTEND[/quote]That probably won't do it if the table contains data.Lemme ask again... what is the business reason for doing this? All ya gotta do is share that teeny bit of info and you can get the help you need. And, just saying it's a requirement won't cut it... I need to know why someone wants to do this. The reason why I want that information up front is because very few people take the time to explain once they've been given the answer.Wed, 21 Jan 2009 06:32:49 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic639222-8-1.aspxThanks friends for all your supporti can achieve this task by the following Query,please let me know your feedbackIF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME = 'Note' AND COLUMN_NAME = 'LastModifyBy' AND DATA_TYPE = 'datetime' )BEGINALTER TABLE [dbo].[Note] ALTER COLUMN LastModifyBy VARCHARUPDATE [dbo].[Note] SET LastModifyBy='0'ALTER TABLE [dbo].[Note] ALTER COLUMN LastModifyBy BIGINTENDTue, 20 Jan 2009 22:57:02 GMTmunishprathaphttp://www.sqlservercentral.com/Forums/Topic639222-8-1.aspx[quote][b]Chris Morris (1/20/2009)[/b][hr]For now, he's still trying to figure out how to persuade someone to write his script without letting on that it's coursework. [/quote]Heh.... then there's that. ;)Tue, 20 Jan 2009 21:28:43 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic639222-8-1.aspx[quote][b]Jeff Moden (1/20/2009)[/b][hr]Doesn't matter if there's 0 data or a billion rows... it's a terrible change to make and if an instructor is teaching someone that this is the right thing to do, (s)he needs to be drawn and quartered. I'm trying to get people to start asking questions instead of just becoming another SQL Clone that blindly does bad things to the database because someone made a bad decision. "Because" is never the correct answer on such things.[/quote]Jeff, you've got a result when the OP submits an answer which consists of a workable script for performing this task - whether we like it or not, there may be marks riding on it - but with the comments "Why you should not do this" and "Here's the best way to do it" - and an understanding of both. For now, he's still trying to figure out how to persuade someone to write his script without letting on that it's coursework. There's a long way to go.Tue, 20 Jan 2009 07:07:18 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic639222-8-1.aspx[quote][b]Chris Morris (1/20/2009)[/b][hr][quote][b]Christopher Stobbs (1/20/2009)[/b][hr]I'm with Jeff on this, why do you need to make the change?[/quote]Because...There's no data in the table, although for the purpose of this exercise we must assume that there are about 10. None of the other students are using the table, of course :Whistling:[/quote]Doesn't matter if there's 0 data or a billion rows... it's a terrible change to make and if an instructor is teaching someone that this is the right thing to do, (s)he needs to be drawn and quartered. I'm trying to get people to start asking questions instead of just becoming another SQL Clone that blindly does bad things to the database because someone made a bad decision. "Because" is never the correct answer on such things.Tue, 20 Jan 2009 06:24:04 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic639222-8-1.aspx[quote][b]Christopher Stobbs (1/20/2009)[/b][hr]I'm with Jeff on this, why do you need to make the change?[/quote]Because...There's no data in the table, although for the purpose of this exercise we must assume that there are about 10. None of the other students are using the table, of course :Whistling:Tue, 20 Jan 2009 06:19:32 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic639222-8-1.aspxI'm with Jeff on this, why do you need to make the change?Tue, 20 Jan 2009 06:12:10 GMTChristopher Stobbshttp://www.sqlservercentral.com/Forums/Topic639222-8-1.aspx[quote][b]munishprathap (1/20/2009)[/b][hr]thy need only to change the data type Datetime to bigint thts it[/quote]WHY??? This is one of the worst things you can do in a database! And just saying it's a requirement isn't the right thing to do either. What is the business need for this horrible change?Tue, 20 Jan 2009 06:00:54 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic639222-8-1.aspx[quote][b]munishprathap (1/20/2009)[/b][hr]in future table will have more than thoushand of recordsat tht time [b]thy dnt need to copy the table content[/b]thy need only to change the data type Datetime to bigint thts it[/quote]Excellent, then use EM to create a new table. Delete the existing one first. Much easier than using those pesky scripts, isn't it?Tue, 20 Jan 2009 05:45:30 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic639222-8-1.aspxin future table will have more than thoushand of recordsat tht time thy dnt need to copy the table contentthy need only to change the data type Datetime to bigint thts itTue, 20 Jan 2009 05:29:26 GMTmunishprathaphttp://www.sqlservercentral.com/Forums/Topic639222-8-1.aspx[quote][b]munishprathap (1/20/2009)[/b][hr]No[/quote]If no other students are using it, why don't you simply script a new table using EM and copy the data into it?Tue, 20 Jan 2009 05:25:00 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic639222-8-1.aspxNoTue, 20 Jan 2009 05:20:58 GMTmunishprathaphttp://www.sqlservercentral.com/Forums/Topic639222-8-1.aspx[quote][b]munishprathap (1/20/2009)[/b][hr]Ok thts great its having 10 rows and one column have DateTime type with values.[/quote]Is this table heavily used at present?Tue, 20 Jan 2009 05:20:01 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic639222-8-1.aspx[quote][b]munishprathap (1/19/2009)[/b][hr]Thanks Jeffaccording to my requirement i need to keep the one column type as bigint but unfortunatelyi keep it as DateTime after long time i found that it was DateTime Type. so i need to write a Script / Query which updates the Column field type as BigInt from DateTime and set the ID Column asIdentity with primary key.[u]My Table Script[/u]CREATE TABLE [dbo].Number ([ID] [BIGINT] NOT NULL,[LastModifyBy] [DATETIME] NOT NULL)[/quote]Please stop and think a minute and I'll give you the answer. What is the purpose of storing a DateTime as a BigInt? I need to know the real reason... not that it's simply a requirement you've been given.Tue, 20 Jan 2009 05:19:53 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic639222-8-1.aspx[quote][b]munishprathap (1/20/2009)[/b][hr]Ok thts great its having 10 rows and one column have DateTime type with values.[/quote]Do these date-time values mean anything? what are your rules for converting a date to integer while still keeping the same value as you will not be able to put a datetime into an int column..Tue, 20 Jan 2009 05:12:56 GMTsteveb. http://www.sqlservercentral.com/Forums/Topic639222-8-1.aspxOk thts great its having 10 rows and one column have DateTime type with values.Tue, 20 Jan 2009 05:00:57 GMTmunishprathaphttp://www.sqlservercentral.com/Forums/Topic639222-8-1.aspx[quote][b]munishprathap (1/20/2009)[/b][hr]we cant predict no. of rows affected assume tht tables have 10 rows[/quote]How many rows do you have in the table [i]right now[/i]?Tue, 20 Jan 2009 04:57:51 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic639222-8-1.aspxwe cant predict no. of rows affected assume tht tables have 10 rowsTue, 20 Jan 2009 04:55:14 GMTmunishprathaphttp://www.sqlservercentral.com/Forums/Topic639222-8-1.aspx[quote][b]munishprathap (1/20/2009)[/b][hr]not lik tht,values entered in these column was datetime only but in future i need to make entriesonly as bigint[/quote]How many rows are currently affected?Tue, 20 Jan 2009 04:50:06 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic639222-8-1.aspxnot lik tht,values entered in these column was datetime only but in future i need to make entriesonly as bigintTue, 20 Jan 2009 04:42:33 GMTmunishprathaphttp://www.sqlservercentral.com/Forums/Topic639222-8-1.aspxSo, for some time bigint values were inserted into datetime column?And now you need to restore them as bigint?Is it right?Tue, 20 Jan 2009 02:54:52 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic639222-8-1.aspxThanks Jeffaccording to my requirement i need to keep the one column type as bigint but unfortunatelyi keep it as DateTime after long time i found that it was DateTime Type. so i need to write a Script / Query which updates the Column field type as BigInt from DateTime and set the ID Column asIdentity with primary key.[u]My Table Script[/u]CREATE TABLE [dbo].Number ([ID] [BIGINT] NOT NULL,[LastModifyBy] [DATETIME] NOT NULL)Mon, 19 Jan 2009 21:49:20 GMTmunishprathaphttp://www.sqlservercentral.com/Forums/Topic639222-8-1.aspxThis is absolutely the wrong thing to do. Dates should not be stored as integers.What are the business rules stating that you should convert the column from a DATETIME datatype to BIGINT? In other words, why do they want to do this?Mon, 19 Jan 2009 19:36:52 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic639222-8-1.aspx[quote][b]munishprathap (1/19/2009)[/b][hr]its a new table didnt hav any values in that column.assume that column 1 has Id as bigint ,column 2 has Date as datetimemy aim is to alter the column 2 type as bigint and column 1 as identity column[/quote]This looks like a straightforward operation - can you post your current table structure?Mon, 19 Jan 2009 06:18:58 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic639222-8-1.aspxShould have mentioned in the first post...If there is no data in the table you can use ALTER TABLE...Mon, 19 Jan 2009 06:12:09 GMTOld McDonaldhttp://www.sqlservercentral.com/Forums/Topic639222-8-1.aspxits a new table didnt hav any values in that column.assume that column 1 has Id as bigint ,column 2 has Date as datetimemy aim is to alter the column 2 type as bigint and column 1 as identity columnMon, 19 Jan 2009 06:05:28 GMTmunishprathaphttp://www.sqlservercentral.com/Forums/Topic639222-8-1.aspx[quote][b]munishprathap (1/19/2009)[/b][hr]i need to convert DateTime to BigInt through query in SQLplz let me know[/quote]What does this column presently contain? Can you post a range of values?Mon, 19 Jan 2009 06:00:38 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic639222-8-1.aspxThanks for ur replybut i need to convert a column of table which has datetime typeplease let me kno the QueryMon, 19 Jan 2009 05:57:24 GMTmunishprathaphttp://www.sqlservercentral.com/Forums/Topic639222-8-1.aspxuse CONVERT or CAST functionMon, 19 Jan 2009 05:47:16 GMTOld McDonaldhttp://www.sqlservercentral.com/Forums/Topic639222-8-1.aspxi need to convert DateTime to BigInt through query in SQLplz let me knowMon, 19 Jan 2009 05:32:22 GMTmunishprathap