September 11, 2008 at 7:24 am
HI
I have a datetime object which i need to insert into myTable into a column that accepts bigint.
Anyone ?
Regards,
Vijoy
September 11, 2008 at 8:20 am
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
September 11, 2008 at 9:18 am
Likely you need a combination of datepart (turns date into numbers) and then possibly a CAST.
September 12, 2008 at 12:00 am
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.
September 12, 2008 at 1:02 am
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
Learn to play, play to learn !
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[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 30, 2008 at 5:32 pm
Hi
I was wondering if you knew the answer how to do the reverse. Going from bigint to datetime?
Thanks!
October 1, 2008 at 3:09 am
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
Learn to play, play to learn !
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[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 1, 2008 at 3:55 pm
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
October 2, 2008 at 12:42 am
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
Learn to play, play to learn !
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[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 6, 2008 at 9:41 pm
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?
October 7, 2008 at 3:18 pm
Thank you all for your responses. With your suggestions i was able to determine that my data was milleseconds since a specific date and from there it was easy to just do the math.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply