Variable Time/Datestamp in Data Query

Kephirah

New Member
Joined
May 2, 2018
Messages
7
Hello all, this is my first post here. I have tried finding an answer to my particular problem and haven't been able to.

This is a snippet of the data query I am trying to write:

SELECT
Source.Item1
Source.Submitted_date
Source.From_ID
FROM
Database.Source
WHERE
(Source.Submitted_Date = { ts '2018-01-01 00:00:00' }) AND (Source.From_ID = ?)


I am trying to create a data query that will pull different information based on the values in cells using parameters. (Source.FROM_ID = ?) works wonderfully, but I can't figure out how to replace the { ts '2018-01-01 00:00:00' } string with a "?". When I try I get the "Enter Parameter Value" dialog, but after choosing it I get this error:

[ORACLE][ODBC][Ora]ORA-01861: literal does not match format string.

My understanding is that the cell is not reflecting that same format as the actual data in the query. Currently, it shows "{ ts '43101 }". I recognize that 43101 is the excel date code, but I can't find a format that shows it as above.
 
How are you replacing { ts '2018-01-01 00:00:00' } in the query?
 
Upvote 0

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Inserting that as
Code:
 WHERE
  CLng(Source.Submitted_Date) = CLng(DateSerial(2018,1,1)) AND (Source.From_ID = ?)
Returns error:
Code:
[Oracle][ODBC][Ora]ORA-00904:"CLNG": invalid identifier

Is INT available instead [of CLng] ?
 
Upvote 0
Is INT available instead [of CLng] ?
Same error, unfortunately.

How are you replacing { ts '2018-01-01 00:00:00' } in the query?
I replace it with a " ? ". I've tried combinations such as replacing the entire string, " {?} " , "{ ts ?}" etc.

I did some more research and it looks like the error may be occurring due to the timestamp portion. If it was just the date, it would be okay. It looks like our database will be getting updated later this year and removing that timestamp from the recorded data, which should remove the problem entirely. I just have to play the waiting game.

Thank you for help! If you have any other suggestions in the meantime, I'll gladly attempt them.
 
Upvote 0

Forum statistics

Threads
1,215,427
Messages
6,124,831
Members
449,190
Latest member
rscraig11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top