convert DateTime to BigInt

Question

convert DateTime to BigInt

Viewing 15 posts - 16 through 30 (of 32 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

munishprathap (1/20/2009)
Ok thts great
its having 10 rows and one column have DateTime type with values.

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..

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 2

munishprathap (1/19/2009)
Thanks Jeff
according to my requirement i need to keep the one column type as bigint but unfortunately
i 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 as
Identity with primary key.
My Table Script
CREATE TABLE [dbo].Number (
[ID] [BIGINT] NOT NULL,
[LastModifyBy] [DATETIME] NOT NULL)

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.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

munishprathap (1/20/2009)
Ok thts great
its having 10 rows and one column have DateTime type with values.

Is this table heavily used at present?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

munishprathap SSChasing Mays Points: 622 More actions · Answer 4

munishprathap

SSChasing Mays

Points: 622

January 20, 2009 at 5:20 am

#929618

No

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

munishprathap (1/20/2009)
No

If no other students are using it, why don't you simply script a new table using EM and copy the data into it?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

munishprathap SSChasing Mays Points: 622 More actions · Answer 6

in future table will have more than thoushand of records

at tht time thy dnt need to copy the table content

thy need only to change the data type Datetime to bigint thts it

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

munishprathap (1/20/2009)
in future table will have more than thoushand of records
at tht time thy dnt need to copy the table content
thy need only to change the data type Datetime to bigint thts it

Excellent, then use EM to create a new table. Delete the existing one first. Much easier than using those pesky scripts, isn't it?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 8

munishprathap (1/20/2009)
thy need only to change the data type Datetime to bigint thts it

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?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 9

I'm with Jeff on this, why do you need to make the change?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Christopher Stobbs (1/20/2009)
I'm with Jeff on this, why do you need to make the change?

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:

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 11

Chris Morris (1/20/2009)
Christopher Stobbs (1/20/2009)
I'm with Jeff on this, why do you need to make the change?
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:

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.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

Jeff Moden (1/20/2009)
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.

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.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 13

Chris Morris (1/20/2009)
For now, he's still trying to figure out how to persuade someone to write his script without letting on that it's coursework.

Heh.... then there's that. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

munishprathap SSChasing Mays Points: 622 More actions · Answer 14

Thanks friends for all your support

i can achieve this task by the following Query,please let me know your feedback

IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = 'Note'

AND COLUMN_NAME = 'LastModifyBy'

AND DATA_TYPE = 'datetime' )

BEGIN

ALTER TABLE [dbo].[Note] ALTER COLUMN LastModifyBy VARCHAR

UPDATE [dbo].[Note]

SET LastModifyBy='0'

ALTER TABLE [dbo].[Note] ALTER COLUMN LastModifyBy BIGINT

END

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 15

munishprathap (1/20/2009)
Thanks friends for all your support
i can achieve this task by the following Query,please let me know your feedback
IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Note'
AND COLUMN_NAME = 'LastModifyBy'
AND DATA_TYPE = 'datetime' )
BEGIN
ALTER TABLE [dbo].[Note] ALTER COLUMN LastModifyBy VARCHAR
UPDATE [dbo].[Note]
SET LastModifyBy='0'
ALTER TABLE [dbo].[Note] ALTER COLUMN LastModifyBy BIGINT
END

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.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)