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 1234»»»

convert DateTime to BigInt Expand / Collapse
Author
Message
Posted Monday, January 19, 2009 5:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 3, 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
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: Wednesday, October 16, 2013 7:46 AM
Points: 533, Visits: 990
use CONVERT or CAST function

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

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 3, 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
Posted Monday, January 19, 2009 6:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:56 AM
Points: 7,120, Visits: 13,491
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
Posted Monday, January 19, 2009 6:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 3, 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
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: Wednesday, October 16, 2013 7:46 AM
Points: 533, Visits: 990
Should have mentioned in the first post...
If there is no data in the table you can use ALTER TABLE...


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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:56 AM
Points: 7,120, Visits: 13,491
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
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: Today @ 7:30 PM
Points: 36,766, Visits: 31,222
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #639609
Posted Monday, January 19, 2009 9:49 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 3, 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
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: Sunday, July 20, 2014 5:23 PM
Points: 4,576, Visits: 8,341
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 1234»»»

Permissions Expand / Collapse