## Convert Bigint time to datetime

 Author Message PSB SSCertifiable Group: General Forum Members Points: 6895 Visits: 1653 SELECT DATEADD(hh,-5,dateadd(s, convert(bigint, 1397750400000) / 1000, convert(datetime, '1-1-1970 00:00:00')))worked for me! Lynn Pettis SSC Guru Group: General Forum Members Points: 226156 Visits: 40424 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 Hall of Fame Group: General Forum Members Points: 3389 Visits: 966 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 SSC Guru Group: General Forum Members Points: 104587 Visits: 15049 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 Corbett Consultant Straight Path Solutions Dont 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 Hall of Fame Group: General Forum Members Points: 3389 Visits: 966 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 SSC Guru Group: General Forum Members Points: 94824 Visits: 20694 PSB (4/17/2014)Minutes`1397750400000 Minutes 23295840000 Hours 970660000 Days 2657522 Years`The first birthday of Homo habilis? Lynn Pettis SSC Guru Group: General Forum Members Points: 226156 Visits: 40424 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: 512382 Visits: 44315 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: 512382 Visits: 44315 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 SSCertifiable Group: General Forum Members Points: 6895 Visits: 1653 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