Forum Discussion
Convert bigint to datetime
Hi there,
How can I convert bigint datatype to date/time datatype in Power BI ?
Thanks
Hi suvechha ,
If it is timestamp, maybe you could try this as well in Query Editor.
DateTimedValue=#datetime(1970,1,1,0,0,0)+#duration(0,0,0,[timestamp])If it is formated as yyyymmdd, you could try this in Data View:
column = DATE(LEFT('Table 1'[Column1],4),MID('Table 1'[Column1],5,2),RIGHT('Table 1'[Column1],2))
5 Replies
it depends on what your time format is? epoch, excel ??
can you provide an example?
Yeah Anonymous it is hard to know what exactly is being asked. Date.From converts the standard Excel style date (integer starting at 0 for Dec 31, 1899) to a date. I've never used that. Just changing the type from Int to Date does that. But I could see it being useful if nested in another formula.
bigint isn't a date time field, it is an integer. Need to know what is in the integer. If it is the YYYYMMDD format, then there are a lot of ways to convert, but:
Date.FromText(Text.From([BigIntField]))is the easiest and most straight forward way.
It could also be a Julian date (common in IBM databases), or Unix Epoch, or other. We'd need to know more as there isn't just one date format stored in integer fields.
Hi suvechha ,
If it is timestamp, maybe you could try this as well in Query Editor.
DateTimedValue=#datetime(1970,1,1,0,0,0)+#duration(0,0,0,[timestamp])If it is formated as yyyymmdd, you could try this in Data View:
column = DATE(LEFT('Table 1'[Column1],4),MID('Table 1'[Column1],5,2),RIGHT('Table 1'[Column1],2))
