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

An odd date from sql - what is it?

josephinetedesc — Thu, 12 Nov 2015 10:15:12 GMT

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.

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

Anonymous — Thu, 12 Nov 2015 10:18:16 GMT

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?

Colin-Albert — Thu, 12 Nov 2015 10:23:55 GMT

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?

tamilarasu — Thu, 12 Nov 2015 10:25:29 GMT

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?

Kushal_Chawda — Thu, 12 Nov 2015 10:26:06 GMT

This is the values for NULL

see the below thread

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

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

buzzy996 — Thu, 12 Nov 2015 10:29:13 GMT

tht's qlikview default time stamp value.

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

josephinetedesc — Thu, 12 Nov 2015 11:08:47 GMT

thread no longer exists ...

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

Peter_Cammaert — Thu, 12 Nov 2015 11:18:44 GMT

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

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

josephinetedesc — Thu, 12 Nov 2015 11:21:09 GMT

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

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

josephinetedesc — Thu, 12 Nov 2015 11:27:49 GMT

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

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

josephinetedesc — Thu, 12 Nov 2015 11:45:01 GMT

timestamp(0)

timestamp(-36161)

This is apparently the Clarion number and Crystal seems to use the Clarion number. So Excel uses 1/1/1900 as day1, but Crystal/Clarion seems to use this other number as their day1 +4 to get the 1/1/1801 .

Thank you all again.