convert a datetime object into bigint

  • HI

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

    Anyone ?

    Regards,

    Vijoy

  • 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

  • Likely you need a combination of datepart (turns date into numbers) and then possibly a CAST.

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

  • 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

  • Hi

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

    Thanks!

  • 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

  • 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

  • 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

  • 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?

  • 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