Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
josephinetedesc
Creator III
Creator 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
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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
Anonymous
Not applicable

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

Colin-Albert

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?

tamilarasu
Champion
Champion

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

Kushal_Chawda

This is the values for NULL

see the below thread

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

buzzy996
Master II
Master II

tht's qlikview default time stamp value.

josephinetedesc
Creator III
Creator III
Author

thread no longer exists ...

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

josephinetedesc
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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