Variable Time/Datestamp in Data Query

[Kephirah]

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.

 

[Norie]

Where is the 43101 coming from?

If you wanted the current date in the format the query appears to require you could use this.

="{ts '" & TEXT(TODAY(), "yyyy-mm-dd hh:mm:ss") & "' }"

 

[Kephirah]

Where is the 43101 coming from?

My apologies; the 43101 is being created by attempt to mimic the format in the query in a cell. I originally used

=DATE(YEAR(TODAY()),1,1)

Formatted to yyyy-mm-dd hh:mm:ss;@ but that didn't work.
I then attempted to recreate the format by using a

=CONCATENATE(F1,G1,H1)

where
F1 ={ '
G1 =DATE(YEAR(TODAY()),1,1)
H1 =' }
Which generates as {ts '43101'}

="{ts '" & TEXT(TODAY(), "yyyy-mm-dd hh:mm:ss") & "' }"

Plugging that into my parameter control cell (D2) I get {ts '2018-05-02 00:00:00'} but still returns the same error message when the query executes.

<tbody>
</tbody><colgroup><col></colgroup>

 

[Kephirah]

Where is the 43101 coming from?

My apologies; the 43101 is being created by attempt to mimic the format in the query in a cell. I originally used

=DATE(YEAR(TODAY()),1,1)

Formatted to yyyy-mm-dd hh:mm:ss;@ but that didn't work.
I then attempted to recreate the format by using a

=CONCATENATE(F1,G1,H1)

where
F1 ={ '
G1 =DATE(YEAR(TODAY()),1,1)
H1 =' }
Which generates as {ts '43101'}

="{ts '" & TEXT(TODAY(), "yyyy-mm-dd hh:mm:ss") & "' }"

Plugging that into my parameter control cell (D2) I get {ts '2018-05-02 00:00:00'} which looks like what I needed (thank you!) but still returns the same error message when the query executes.

<tbody>
</tbody>

 

[Norie]

Try what I suggested but without the { ts } part, so something like this,

="'" & TEXT(TODAY(), "yyyy-mm-dd hh:mm:ss") & "'"

or even this.

=TEXT(TODAY(), "yyyy-mm-dd hh:mm:ss")

 

[Kephirah]

Trying both of those, it still allows me to enter the cell as the parameter value but returns the same error.

 

[Norie]

Which version of Excel are you using and how are you setting up the query?

 

[Kephirah]

Excel 2016
Data > From Other Sources > From Microsoft Query > Database and desired data > Connection Properties > Definition > SQL in the Command Text area

Hope I understood the question

 

[Fazza]

Instead of
WHERE (Source.Submitted_Date = { ts '2018-01-01 00:00:00' })

try
WHERE CLng(Source.Submitted_Date) = CLng(DateSerial(2018,1,1))

 

[Kephirah]

Instead of
WHERE (Source.Submitted_Date = { ts '2018-01-01 00:00:00' })

try
WHERE CLng(Source.Submitted_Date) = CLng(DateSerial(2018,1,1))

Inserting that as

 WHERE
  CLng(Source.Submitted_Date) = CLng(DateSerial(2018,1,1)) AND (Source.From_ID = ?)

Returns error:

[Oracle][ODBC][Ora]ORA-00904:"CLNG": invalid identifier