Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

josephinetedesc
Contributor III

An odd date from sql - what is it?

1801-01-01 00:00:00.000

Hi all when I do a query in SQL I get a date that looks like the date above.

Can anone tell me what it is?  Is it a hex code for null or something???  Qlikview gives me the same thing.

Jo

1 Solution

Accepted Solutions

Re: An odd date from sql - what is it?

Some background info:

weird dates like this usually represent the binary 0 value for an otherwise valid DateTime field. DateTime values are kept as a numerical value, most often a number of seconds since a starting date (often called epoch).

The starting moment is chosen by the DB manufacturer. In this case value 0 = 1st second of Jan 1st, 1801. Different DB vendors will use a different start date. Microsoft chose Dec 31, 1899 for the zero value in Excel and as a result that is the starting value for Timestamps in QlikView as well (try a text box with an expression like =TimeStamp(0) )

Best,

Peter

View solution in original post

10 Replies
balrajahlawat
Esteemed Contributor

Re: An odd date from sql - what is it?

It seems like a junk value or system defined date, would suggest interpret the date fields with new date format..

Re: An odd date from sql - what is it?

Looks like a timestamp value for midnight 1st Jan 1801.

formatted as YYYY-MM-DD  hh:mm:ss.fff

I have come across some applications that insert a base date value rather than nulls in empty or blank date fields.

What is the next earliest date in the system?

Re: An odd date from sql - what is it?

Hi,

You need to convert this in Sql. Search about clarion date in google, you will know about this.  Check the below link.

gist.github.com/fushnisoft/5857270

Re: An odd date from sql - what is it?

This is the values for NULL

see the below thread

http://www.sqlservercentral.com/Forums/Topic1152065-8-1.aspx

buzzy996
Honored Contributor II

Re: An odd date from sql - what is it?

tht's qlikview default time stamp value.

josephinetedesc
Contributor III

Re: An odd date from sql - what is it?

thread no longer exists ...

Re: An odd date from sql - what is it?

Some background info:

weird dates like this usually represent the binary 0 value for an otherwise valid DateTime field. DateTime values are kept as a numerical value, most often a number of seconds since a starting date (often called epoch).

The starting moment is chosen by the DB manufacturer. In this case value 0 = 1st second of Jan 1st, 1801. Different DB vendors will use a different start date. Microsoft chose Dec 31, 1899 for the zero value in Excel and as a result that is the starting value for Timestamps in QlikView as well (try a text box with an expression like =TimeStamp(0) )

Best,

Peter

View solution in original post

josephinetedesc
Contributor III

Re: An odd date from sql - what is it?

When a date occurs then it is correctly shown:

11/11/2015

one is left aligned the other right aligned:

 

1801-01-01 00:00:00.000
11/11/2015
josephinetedesc
Contributor III

Re: An odd date from sql - what is it?

Clarion dates in Crystal parameters - Business Objects: Crystal Reports 4 Other topics - Tek-Tips

Crystal is the program that comes with the package ... so makes sense ... The value is overwritten when the value changes because it has been edited or something has happened.

Thank you all