cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Sully
Level III

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
Super User (Alumni)

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

View solution in original post

3 REPLIES 3
cwillden
Super User (Alumni)

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
Level III

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
Super User (Alumni)

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