how to convert a bigint value to datetime

Question

Post reply

how to convert a bigint value to datetime

bhuwnesh.kumar

Grasshopper

Points: 17
More actions
March 27, 2009 at 12:11 am

#384231

In a table, datetime is stored in a column with type bigint. Values stored in this column are of 13 digits.
How these values can be converted to simple datetime format.
Convert function is throwing 'Arithmetic overflow error'.

Viewing 4 posts - 1 through 4 (of 4 total)

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

arun.sas SSChampion Points: 11831 More actions · Answer 1

HI,

Try this code

declare @abc table (TEST bigint)

declare @DATE1 datetime

select @DATE1 = getdate()

insert into @abc values (convert(bigint,@DATE1))

01)

select * from @abc

RESULT

39898

02)

select convert(datetime,39898)

RESULT

2009-03-28 00:00:00.000

ARUN SAS

Dugi SSCoach Points: 17998 More actions · Answer 2

http://msdn.microsoft.com/en-us/library/aa226054(SQL.80).aspx

This is the location where you can find everything about converting, also with examples!

Whistling:

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

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

bhuwnesh.kumar (3/27/2009)
In a table, datetime is stored in a column with type bigint. Values stored in this column are of 13 digits.
How these values can be converted to simple datetime format.
Convert function is throwing 'Arithmetic overflow error'.

Can you provide a few samples please?

^{“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