Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 7,2000 » T-SQL » convert DateTime to BigInt
Add to briefcase
33 posts, Page 1 of 4
1234»»»

convert DateTime to BigInt Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
munishprathap
Posted Monday, January 19, 2009 5:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 03, 2009 5:24 AM
Points: 10, Visits: 18
i need to convert DateTime to BigInt through query in SQL
plz let me know
Post #639222
Old McDonald
Posted Monday, January 19, 2009 5:47 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: Thursday, April 11, 2013 8:16 AM
Points: 532, Visits: 980
use CONVERT or CAST function

-Vikas Bindra
Post #639234
munishprathap
Posted Monday, January 19, 2009 5:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 03, 2009 5:24 AM
Points: 10, Visits: 18
Thanks for ur reply
but i need to convert a column of table which has datetime type
please let me kno the Query
Post #639241
ChrisM@Work
Posted Monday, January 19, 2009 6:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:48 AM
Points: 5,602, Visits: 10,952
munishprathap (1/19/2009)
i need to convert DateTime to BigInt through query in SQL
plz let me know


What does this column presently contain? Can you post a range of values?

“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
Exploring Recursive CTEs by Example Dwain Camps
Post #639244
munishprathap
Posted Monday, January 19, 2009 6:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 03, 2009 5:24 AM
Points: 10, Visits: 18
its a new table didnt hav any values in that column.
assume that column 1 has Id as bigint ,column 2 has Date as datetime
my aim is to alter the column 2 type as bigint and column 1 as identity column
Post #639248
Old McDonald
Posted Monday, January 19, 2009 6:12 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: Thursday, April 11, 2013 8:16 AM
Points: 532, Visits: 980
Should have mentioned in the first post...
If there is no data in the table you can use ALTER TABLE...

-Vikas Bindra
Post #639257
ChrisM@Work
Posted Monday, January 19, 2009 6:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:48 AM
Points: 5,602, Visits: 10,952
munishprathap (1/19/2009)
its a new table didnt hav any values in that column.
assume that column 1 has Id as bigint ,column 2 has Date as datetime
my aim is to alter the column 2 type as bigint and column 1 as identity column


This looks like a straightforward operation - can you post your current table structure?

“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
Exploring Recursive CTEs by Example Dwain Camps
Post #639262
Jeff Moden
Posted Monday, January 19, 2009 7:36 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:57 PM
Points: 32,893, Visits: 26,771
This 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?

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

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #639609
munishprathap
Posted Monday, January 19, 2009 9:49 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 03, 2009 5:24 AM
Points: 10, Visits: 18
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)
Post #639640
Sergiy
Posted Tuesday, January 20, 2009 2:54 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 5:01 PM
Points: 4,540, Visits: 8,184
So, for some time bigint values were inserted into datetime column?
And now you need to restore them as bigint?
Is it right?
Post #639743
« Prev Topic | Next Topic »

Add to briefcase
33 posts, Page 1 of 4
1234»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.