## Convert Bigint time to datetime

 Author Message PSB SSC Eights! Group: General Forum Members Points: 977 Visits: 1543 SELECT DATEADD(hh,-5,dateadd(s, convert(bigint, 1397750400000) / 1000, convert(datetime, '1-1-1970 00:00:00')))worked for me! Lynn Pettis SSC-Dedicated Group: General Forum Members Points: 39133 Visits: 38518 PSB (4/17/2014)SELECT DATEADD(hh,-5,dateadd(s, convert(bigint, 1397750400000) / 1000, convert(datetime, '1-1-1970 00:00:00')))worked for me!So the BIGINT value was actually milliseconds since midnight 1970-01-01, not minutes. Good to know. Lynn PettisFor better assistance in answering your questions, click hereFor tips to get better help with Performance Problems, click hereFor Running Totals and its variations, click here or when working with partitioned tablesFor more about Tally Tables, click hereFor more about Cross Tabs and Pivots, click here and hereManaging Transaction LogsSQL Musings from the Desert Fountain Valley SQL (My Mirror Blog) gbritton1 SSChasing Mays Group: General Forum Members Points: 641 Visits: 864 Something to consider:`select datediff(m, '0001-01-01', '9999-12-31')`returns 119987That is the largest difference in minutes between the earliest and latest dates handled by SQL Server. The earliest date it can store is Jan 1 1753, IIRC.Is 1397750400000 the minute of the Big Bang or something like that? Jack Corbett SSCoach Group: General Forum Members Points: 18402 Visits: 14893 gbritton1 (4/17/2014)Something to consider:`select datediff(m, '0001-01-01', '9999-12-31')`returns 119987That is the largest difference in minutes between the earliest and latest dates handled by SQL Server. The earliest date it can store is Jan 1 1753, IIRC.Is 1397750400000 the minute of the Big Bang or something like that?the "m" is MONTH not minute, the shorthand for minute is mi or n. That's why I always spell out the full identifier: MONTH, YEAR, DAY, MINUTE, HOUR, SECOND, MILLISECOND instead of the abbreviation. Jack CorbettApplications Developer Don't let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck out these links on how to get faster and more accurate answers: Forum Etiquette: How to post data/code on a forum to get the best helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance ProblemsCrosstabs and Pivots or How to turn rows into columns Part 1Crosstabs and Pivots or How to turn rows into columns Part 2 gbritton1 SSChasing Mays Group: General Forum Members Points: 641 Visits: 864 Sorry, you're quite right! That should be:`declare @maxminutes bigint = datediff(day, '0001-01-01', '9999-12-31')*cast(24*60 as bigint)select @maxminutes`which yields: 5258963520 Eirikur Eiriksson SSCoach Group: General Forum Members Points: 15013 Visits: 18592 PSB (4/17/2014)Minutes`1397750400000 Minutes 23295840000 Hours 970660000 Days 2657522 Years`The first birthday of Homo habilis? Lynn Pettis SSC-Dedicated Group: General Forum Members Points: 39133 Visits: 38518 Eirikur Eiriksson (4/20/2014)PSB (4/17/2014)Minutes`1397750400000 Minutes 23295840000 Hours 970660000 Days 2657522 Years`The first birthday of Homo habilis?Actually, it was milliseconds. ;-) Lynn PettisFor better assistance in answering your questions, click hereFor tips to get better help with Performance Problems, click hereFor Running Totals and its variations, click here or when working with partitioned tablesFor more about Tally Tables, click hereFor more about Cross Tabs and Pivots, click here and hereManaging Transaction LogsSQL Musings from the Desert Fountain Valley SQL (My Mirror Blog) Jeff Moden SSC Guru Group: General Forum Members Points: 85450 Visits: 41079 gbritton1 (4/17/2014)Just do it in a loop, ...I hope your not serious. Let's see the code that does it in a loop. --Jeff ModenRBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs Jeff Moden SSC Guru Group: General Forum Members Points: 85450 Visits: 41079 Jack Corbett (4/17/2014)gbritton1 (4/17/2014)Something to consider:`select datediff(m, '0001-01-01', '9999-12-31')`returns 119987That is the largest difference in minutes between the earliest and latest dates handled by SQL Server. The earliest date it can store is Jan 1 1753, IIRC.Is 1397750400000 the minute of the Big Bang or something like that?the "m" is MONTH not minute, the shorthand for minute is mi or n. That's why I always spell out the full identifier: MONTH, YEAR, DAY, MINUTE, HOUR, SECOND, MILLISECOND instead of the abbreviation.Heh... oddly enough, that's why I use the 2 character abbreviations for dateparts. --Jeff ModenRBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs PSB SSC Eights! Group: General Forum Members Points: 977 Visits: 1543 I have a couple of 14 digit int time in my tableSELECT DATEADD(hh,-5,dateadd(s, convert(bigint, 61353491400000) / 1000, convert(datetime, '1-1-1970 00:00:00')))The above query throws an error :Arithmetic overflow error converting expression to data type int.Please advise how di I resolve it.Thanks,PSB