My database stores all time measures as datetime. For my analyses, I need no more than date.
When I write the SQL statement in DB Query Reader, I use a line such as
CAST(Patient.Birth_DtTm AS date) AS DateOfBirth,
CONVERT(date, Patient.Birth_DtTm, 120) as DateOfBirth2,
These commands return the correct date as a string, but not as a date.
Is there some way of returning the date as a date variable in KNIME rather than a string?
A
I don’t have a SQL solution. Why not convert with the String to Date&Time node?
I was hoping to reduce the number of modules used, for the sake of tidiness!
I don’t know that it is a SQL problem anyway. Similar conversions when queried in SQL reply that the output is a date.
A
Hi @AAM , what type of database are you using? (Guessing at either Sybase or SQL Server given the use of CONVERT function).
I’m surprised the CAST is returning a string.
Have you tried CONVERT without the “, 120”
ie
CONVERT(date, Patient.Birth_DtTm) as DateOfBirth2
Since the “120” argument is used to return a specific string representation.
MSSQL
The CONVERT function operates the same whether there is 120 there or not. One post suggested that 120 made the SQL output into a ‘proper’ date format, which is why I tried it. But the output is exactly the same as the CAST function.
I am unsure whether the issue is with SQL or with KNIME’s interpretation of the data. Which is why I asked.
A
Hi @AAM , thanks for the info. If I get a chance later I’ll have a play with an mssql database and see what results I get.
Hi @aam,
I could not explain why your MSSQL was behaving the way it is. I found an existing MSSQL database that I could connect to and run a sample query similar to yours. The results are that it returns these fields as DATE and not Varchar/String columns
As I would expect, both
cast(xxxxxx as date)
and
convert(date, xxxxxx)
return DATE columns from a DATETIME column xxxxxx
The suggestion of the 120 argument being used in this case to output a “proper” date format is, I believe, erroneous. a Date is a “date” and has no inherent “format”. The only time a date has a format is when it is rendered as a string. The format of a date we see in KNIME is KNIME’s representation of the date, and is not the date itself. I believe that the 120, in the context of cast to date is ignored, which is why I also threw in an example 99999 in its place in the above screenshot. As you can see, any number can be supplied as the third parameter in cast(date, xxxxxx , ? ) and does not affect the result.
So that left me wondering why you are not seeing dates.
The answer to that is, I believe, likely to be the jdbc driver that you are using. The above result was achieved using either of the official Microsoft SQL Server drivers shown below.
If, however, I use the jTDS driver, this is the result:
As you can see, for the jTDS driver, the dates are returned as String columns. So this is actually a jdbc driver issue rather than a KNIME issue, and it appears to be a known bug in jTDS.
Do you have the Microsoft jdbc driver available to try? If not, you may need to install the Microsoft SQL extension in KNIME
With this in mind, a google search for knime jtds database driver date reveals a similar post:
Of course, now that we have a reason, the alternative approach is as @rfeigel 's suggestion of converting the string after it is returned.
A second option, if you don’t want to add the additional node could be that you go to the Input Type Mapping tab on the DB Query reader, and specify the mapping for particular date columns. In this case I used regex for any column I had beginning “cdate”. You may be able to do something similar.
You can also do something similar on the actual Microsoft SQL Server Connector:
Here, I have said that any column containing the word “date” followed by optional digits should be returned as a date:
I hope that helps.
Thank you, I only have the jTDC driver available, hence your explanation is reality here.
I’ll work on the next bit.
A
I’ve tried all of that and it has been unsuccessful unfortunately, but thanks.
A
