BookmarkSubscribeRSS Feed
Sully

Community Trekker

Joined:

Aug 24, 2017

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

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
cwillden

Community Trekker

Joined:

May 1, 2017

Solution

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

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

 

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.

-- Cameron Willden
3 REPLIES
Highlighted
cwillden

Community Trekker

Joined:

May 1, 2017

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

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?

-- Cameron Willden
Sully

Community Trekker

Joined:

Aug 24, 2017

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

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 ();

 

cwillden

Community Trekker

Joined:

May 1, 2017

Solution

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

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

 

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.

-- Cameron Willden