SQlite Database - tick dates

[Dgasld77]

Hi all,

I've succesfully connected to an Sqlite database and the date values are in "Tick" format.

They've pulled values in like the following  (DDMMYYYY)

11/02/6368 5:50:00 AM

I can successfully use formulas to change the above characters to reflect an actual date.

However, some values are appearing like the below:

1/01/0001 12:00:00 AM
I assume this is an error.

Can anyone help me as to why this is occurring?

Cheers

[Anonymous]

Hi @Dgasld77 ,

I think this is the value(1/01/0001) corresponding to the null.

You could use IF( ISBLANK(), BLANK(), FORMAT( ) )  to avoid this error.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Eyelyn Qin

[amitchandak]

@Dgasld77 , Did get date in this format -DDMMYYYY

Then you can create a date like

New Date = date(right([Date_Field],4),mid([Date_Field],3,2),left([Date_Field],2))

For DDMM, you need to enable UK/AUS format of date other wise

https://community.powerbi.com/t5/Desktop/How-to-apply-UK-date-format-dd-mm-yyyy-in-Date-slicer/td-p/...

[Anonymous]

Thanks very much for the reply.

However, the changing of format to a normal date isn't the issue.  It's the fact that there are errors where dates should be.

The errors appear like:    1/01/0001 12:00:00 AM

What could possibly be the problem?

Is it something between the "Source" and "Navigation" steps in Power BI?