SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


convert a datetime object into bigint


convert a datetime object into bigint

Author
Message
Vijoy Kolady
Vijoy Kolady
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 60
HI

I have a datetime object which i need to insert into myTable into a column that accepts bigint.

Anyone ?

Regards,
Vijoy
GSquared
GSquared
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55233 Visits: 9730
How you do that depends on the end result you want.

For example, today is 11 Sept 08. That could be represented in bigint as 20080911, or it could be 39700 (the number of days since 1/1/1900).

(If you simply cast a datetime as float or int or whatever, it is the number of days since 1/1/1900.)

What end-result are you going for? One of these, or something else?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Steve Jones
Steve Jones
SSC Guru
SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)

Group: Administrators
Points: 140939 Visits: 19415
Likely you need a combination of datepart (turns date into numbers) and then possibly a CAST.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Vijoy Kolady
Vijoy Kolady
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 60
thanks everyone. The script worked fine with the convert function.

I need to convert an nvarchar timezone field to bigint. Can anyone quickly tell me the solution to this as my nvarchar field contains timezone values like this --> GMT-05:00

looking forward for a prompt response.
ALZDBA
ALZDBA
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28507 Visits: 8986
If you want datetime info, your best choice is a column of the datetime data type !
You'll get a bunch of datetime functions to surve you !

Rule number 3: use the correct data type !

You can use:

Declare @mybigint bigint
set @mybigint =
convert(bigint,replace(replace(replace(replace(convert(char(23), getdate(), 121),'-',''),':',''),'.',''),' ',''))
print @mybigint



Timezone ? You'll need another convert function to substring and reformat it.

btw: SQL2008 will have implicit timezone features with a new datetime data type.
(datetimeoffset)



Keep in mind, you'll end up with converting it back to datetime stuff, so have a second thought about it !

Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
mblack-797701
mblack-797701
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
Points: 124 Visits: 26
Hi

I was wondering if you knew the answer how to do the reverse. Going from bigint to datetime?

Thanks!
ALZDBA
ALZDBA
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28507 Visits: 8986
how about this:
Declare @mybigint  bigint
set @mybigint =
convert(bigint,replace(replace(replace(replace(convert(char(23), getdate(), 121),'-',''),':',''),'.',''),' ',''))
print @mybigint


declare @mychar char(23)
Select @mychar = convert(char(23), @mybigint)
Select @mychar = substring(@mychar,1,4) + '-' + substring(@mychar,5,2) + '-' + substring(@mychar,7,2) + ' '
+ substring(@mychar,9,2) + ':' + substring(@mychar,11,2) + ':' + substring(@mychar,13,2)
+ '.'+ substring(@mychar,15,3)

declare @mydatetime datetime
set @mydatetime = convert(datetime,@mychar,121)
print convert(char(23),@mydatetime,121)



Check out "convert" in bol

Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
mblack-797701
mblack-797701
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
Points: 124 Visits: 26
Thank you for your reply. As i looked at my data and what your SQL was doing, it doesn't appear that my numbers are stripped out time stamps. In one of the above posts a guy mentions that sometimes it's stored as the number of days since 1/1/1900. Do you know how to take a bigint like that and make it a normal date?

Here's some sample "dates" that i'm working with:
1196100822809
1196108435656
1196100810760
1196108412559
1196701846338
1196100817789
1196100854938
1196108456761
1196701886504
ALZDBA
ALZDBA
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28507 Visits: 8986
Nice ... the N-th datetime solutions payback time Hehe


Just for correctness ...

can you elaborate and split your bigint into the date part and the timepart ?

1196100822809

1196 = NoDays
10= HH
08 = mm
22 = ss
809 = ms

Correct ?


Declare @mybigint bigint
set @mybigint = 1196100822809

print @mybigint

declare @mychar char(23)
declare @mycharwrk char(23)
Select @mychar = convert(char(23), @mybigint )
print cast(reverse(substring(ltrim(reverse(@mychar)), 10,10)) as int)

select @mycharwrk = convert(char(10),dateadd(dd,cast(reverse(substring(ltrim(reverse(@mychar)), 10,10)) as int),'1900-01-01'),121)
+ ' ' + reverse(substring(ltrim(reverse(@mychar)), 8,2))
+ ':' + reverse(substring(ltrim(reverse(@mychar)), 6,2))
+ ':' + reverse(substring(ltrim(reverse(@mychar)), 4,2))
+ '.' + reverse(substring(ltrim(reverse(@mychar)), 1,3))

print @mycharwrk

declare @mydatetime datetime
set @mydatetime = convert(datetime,@mycharwrk,121)
print convert(char(23),@mydatetime,121)

Print 'Did you notice the change in ms ?? !! '




Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
jabarker
jabarker
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 128
It would help to know the source of the data. For example, PHP programs tend to store times as the number of seconds since January 1, 1970 - in other words as Unix timestamps.

Your data looks like it could be milliseconds since the Epoch (1/1/1970). Would that make sense?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search