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

convert a datetime object into bigint Expand / Collapse
Author
Message
Posted Thursday, September 11, 2008 7:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 9, 2008 4:07 AM
Points: 17, Visits: 60
HI

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

Anyone ?

Regards,
Vijoy
Post #567704
Posted Thursday, September 11, 2008 8:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #567755
Posted Thursday, September 11, 2008 9:18 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 10:36 AM
Points: 31,362, Visits: 15,823
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
Post #567839
Posted Friday, September 12, 2008 12:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 9, 2008 4:07 AM
Points: 17, 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.
Post #568263
Posted Friday, September 12, 2008 1:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 5:03 AM
Points: 6,748, Visits: 8,544
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #568291
Posted Tuesday, September 30, 2008 5:32 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 25, 2008 2:14 PM
Points: 14, Visits: 26
Hi

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

Thanks!
Post #578769
Posted Wednesday, October 1, 2008 3:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 5:03 AM
Points: 6,748, Visits: 8,544
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #578920
Posted Wednesday, October 1, 2008 3:55 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 25, 2008 2:14 PM
Points: 14, 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
Post #579416
Posted Thursday, October 2, 2008 12:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 5:03 AM
Points: 6,748, Visits: 8,544
Nice ... the N-th datetime solutions payback time


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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #579498
Posted Monday, October 6, 2008 9:41 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 18, 2014 10:12 AM
Points: 40, Visits: 120
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?
Post #581490
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse