An odd date from sql - what is it?

[josephinetedesc]

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

[Anonymous]

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]

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]

tht's qlikview default time stamp value.

[josephinetedesc]

thread no longer exists ...

[Peter_Cammaert · Accepted solution]

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]

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]

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