I have a table in SQL. I have the following query against said table:
SELECT coalesce(setdate,'') as datetime from error WHERE fault = 1
Now I can take SQL manager and look at the table and the first 2 rows of data have an actual value in them. The third row has a NULL in it. However when I use this query to generate data for a table in FPMI, the row with NULL in it gets a date of Jan 1st 1900. And a time of 12:00:00am
Can someone explain what is happening here?
Thanks and have a great day.
PS what I want the display to do is show the first two time stamps and because the third is a null display a blank not some obscure date.
Are you sure the date is 1900? Not 1969?
If its 1900 I’m confused, but if its 1969 I have an explanation. While you’re at it, what version of FactoryPMI are you using?
Oh, one more thing - what database are you using?
mrtweaver,
Assuming you are displaying in a table, I think your best bet is to use the table customizer. You can use the “Translation List” for the column to show a blank whenever it sees the Jan 1st 1900 date.
First off what data base am I using: That would be SQL 2005
Second what version of PMI am I using: That would be 3.2.0 build(1898)
Third what is the date shown: That would be Jan 1st 1900, 12:00am (shown just as is)
Fourth is the translation table: I did try it but it did not work I entered in the data just as it is shown on screen and how I typed it above and it acted like that data was not there and did nothing to it.
I also tried to formulate a case when statement because I thought maybe when it came to the NULL it translated the word NULL to the date in question. This did not work either.
The column for the SQL table is set as datetime.
Hope this helps.
mrtweaver,
The translation list would not work because the data is actually coming through in a different format than you are seeing. it will look like yyyy-mm-dd hh:mm:ss. I guess in your case, 1900-01-01 00:00:00 would need to be entered in the translation list.
Usually, when a database sees a NULL in a timestamp column, it translates it to the epoch (usually 1969-12-31 16:00:00 or in Linux 1970-01-01 00:00:00) because a timestamp cannot be a blank string. This is because of the way dates are stored; they use the number of seconds since the epoch rather than a string for the current time.
Your CASE WHEN statement should also work, but NULL is not a string. NULL means “I am not equal to anything, not even myself.” A CASE WHEN statement should use IS NULL and look like:SELECT CASE
WHEN setdate IS NULL
THEN ""
ELSE setdate
END as "datetime"IS is a keyword used for finding null values in your database.
I hope that isn’t way more information than you were asking about!
Just to clarify a few things. The Linux/Unix/Java epoch is always 1970-01-01 00:00:00. It only looks like 1969-12-31 16:00:00 to us because we’re in California! (GMT-0800). Also, timestamps can be null in a database. MySQL is just in the habit of storing nulls as zeros, which you can correct by specifying zeroDateTimeBehavior=convertToNull; on the connection. Maybe SQL Server is in this same habit as well…
It turns out that 1900-01-01 0:00:00 is the SQL Server epoch. This means that instead of sending NULL, SQL Server is sending zero (either that, or the JDBC driver is interpreting the NULL as a zero, hard to tell). As you’re noticing, there is a big difference between null and zero.
The translation map should work, but unfortunately the string that you’re trying to match isn’t what you’re expecting, (it’ll be something like Mon Jan 01 00:00:00 PST 1900, but different because you’re not in the same timezone I’m in). That is actually an oversigt on our part - in future releases the string that you’d try to match will be what Robert suggested (1900-01-01 00:00:00)
Translating it using a CASE in the query should always work - you just have to get your CASE right like Robert said.
Hope this helps,