Convert bigint to datetime

[suvechha]

Hi there,

How can I convert bigint datatype to date/time datatype in Power BI ?

Thanks

[v-xuding-msft · Accepted solution]

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

[edhans]

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.

[fuglphoenix]

it depends on what your time format is? epoch, excel ??

can you provide an example?

[Anonymous]

peraphs he is refering to the function

date.from(value)

[edhans]

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.