topic Is it possible to convert timestamp date to date in Database Query JSL? in Discussions

Is it possible to convert timestamp date to date in Database Query JSL?

Sully — Thu, 08 Nov 2018 20:05:50 GMT

Hello,

I have been searching all over to no avail. I have a new database that I am pulling from and all dates located within are timestamped. I am hoping to get some help on being able to query in this database using just a date and not timestamps using JSL. The column I am using to query is a Numeric Continuous Date column that it's format is m/d/y h:m:s (Width 30 Dec 6) and informat is m/d/y h:m:s

Re: Is it possible to convert timestamp date to date in Database Query JSL?

cwillden — Thu, 08 Nov 2018 20:15:14 GMT

Are you trying to query dates in a range? This might be a dumb question, but why not just use midnight for the time portion?

Re: Is it possible to convert timestamp date to date in Database Query JSL?

Sully — Thu, 08 Nov 2018 20:28:05 GMT

I am querying betwen a date range. Perhaps I am overthinking this as I haven't queried using timestamps before (example of what the dates look like in the DB are below).

11/02/2018 10:53:01.465000 AM

11/02/2018 12:40:43.716000 PM

Example of my query is below:

Open Database (

"DSN = PROD; UID= " || ID || " , PWD= " || PASS || "; MODE = SHARE; DBALIAS = PROD;",

"SELECT *

FROM pODSCFView.FRD_TABLE

WHERE TRN_DT >= date and TRN_DT <= date");

DT1 = Current Data Table ();

Re: Is it possible to convert timestamp date to date in Database Query JSL?

cwillden — Thu, 08 Nov 2018 20:57:37 GMT

You can try the suggestions here: https://stackoverflow.com/questions/22506930/how-to-query-datetime-field-using-only-date-in-sql-server/30707121

Or just use use midnight as the time cutoff. E.g. WHERE TRN_DT >= '11/02/2018 12:00:00.000000 AM' and TRN_DT < '11/03/2018 12:00:00.000000 AM' ); would return any results with any time stamp on 11/2/18.