concat datefield and time field without losing seconds

[Quigwam2058]

I am using a hosted product that uses Cognos to report, I have a datefield with which is formatted as 09/22/2022 when used in my list report.  I also have a timefield - 14:08:22

When I join the two fields using pipes, I lose the seconds from the timefield, but I need this in my query.  Is there a function available that joins these two fields and retains the seconds? 

Ultimately I am trying to identify the most current record by concatenating the date and time fields.

Thank you.

[bus_pass_man]

Are you sure that there isn't a timestamp column which contains both the date and the time, from which your two columns were dervived, hanging around?

Basically build up a string which represents the value of the timestamp and cast it to timestamp.  Here is a very quickly put together example. 
cast (
substring(
cast( SHIP_DAY_KEY  as varchar(10)) ,1,4 ) || '-'
+
  substring(
cast( SHIP_DAY_KEY  as varchar(10)) ,5,2 )|| '-'
||
substring(
cast( SHIP_DAY_KEY  as varchar(10)) ,7,2 )
||
'T04:25:39'

, timestamp)

[MFGF]

I am using a hosted product that uses Cognos to report, I have a datefield with which is formatted as 09/22/2022 when used in my list report.  I also have a timefield - 14:08:22

When I join the two fields using pipes, I lose the seconds from the timefield, but I need this in my query.  Is there a function available that joins these two fields and retains the seconds? 

Ultimately I am trying to identify the most current record by concatenating the date and time fields.

Thank you.

Hi,

Assuming the initial item has a data type of Date in your report, and the second item has a data type of Time in your report, you should just be able to do this in a query calculation:

cast([Your Date Item] + [Your Time Item], timestamp)

Does that work for you?

Cheers!

MF.

[Quigwam2058]

Thank you, this does not return seconds.  I was using the pipe symbol, but that also did not return seconds.

[Date - Emp Activity History]||[Time - Emp Activity History]

[MFGF]

Thank you, this does not return seconds.  I was using the pipe symbol, but that also did not return seconds.

[Date - Emp Activity History]||[Time - Emp Activity History]

Can you tell us what exact data types the Date - Emp Activity History and Time - Emp Activity History items are?

I had two items that were a Date data type and a Time data type, and the expression I posted worked fine?

Cheers!

MF.

[Quigwam2058]

The time field is a string, and not a time format, so first I converted it using substring as described above by bus_pass_Man

• 
  substring(
  cast([Time - Emp Activity History]  as varchar(10)) ,1,4 ) || '-'
  +
    substring(
  cast([Time - Emp Activity History]  as varchar(10)) ,5,2 )|| '-'
  ||
  substring(
  cast([Time - Emp Activity History] as varchar(10)) ,7,2 )

but I then get an error when trying to use this to create datetimestamp as mentioned by MFGF, because the resulting string is not a time format and if I try first to cast to time, I receive an error as well.

• cast([Date - Emp Activity History] + cast ([Data Item1], time), timestamp)

[MFGF]

The time field is a string, and not a time format, so first I converted it using substring as described above by bus_pass_Man

• 
  substring(
  cast([Time - Emp Activity History]  as varchar(10)) ,1,4 ) || '-'
  +
    substring(
  cast([Time - Emp Activity History]  as varchar(10)) ,5,2 )|| '-'
  ||
  substring(
  cast([Time - Emp Activity History] as varchar(10)) ,7,2 )

but I then get an error when trying to use this to create datetimestamp as mentioned by MFGF, because the resulting string is not a time format and if I try first to cast to time, I receive an error as well.

• cast([Date - Emp Activity History] + cast ([Data Item1], time), timestamp)

If it's in the format 14:08:22, what happens if you just initially cast it as-is to a time data type:

cast([Time - Emp Activity History], time)

Can you then use it in the expression for the timestamp?

Cheers!

MF.

[Quigwam2058]

Yes, that works - thank you. 

[Quigwam2058]

I am wondering why this did not work?  It is essentially the same thing, in the one step rather than two.
cast ([Date - Emp Activity History] + cast([Time - Emp Activity History], Time), timestamp)

Is the syntax wrong.  I got the following error:

RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-126'.
UDA-SQL-0460 A general exception has occurred during local processing.
UDA-EE-0094 The operation "add" is invalid for the following combination of data types: "timestamp2" and "timestamp2"
RSV-SRV-0042 Trace back:

[bus_pass_man]

The error message is complaining that it can't add two timestamps.  The + operator is used for addition as well as concatenation.



The format of the string to be converted to a timestamp needs to be: yyyy-mm-ddThh:mm:ss

It doesn't matter how you get that, you just need it to be in that format.  To build up the time part of the string you need to use the same method as you use in building up the date part.

The format of the string to be converted to a date needs to be: yyyy-mm-dd

If you build the expression in a data module you can use the functionality there where you can select a subset of your expression and test it.





Are you really sure that there isn't a timestamp column which contains both the date and the time, from which your two columns were derived, hanging around?

The time field is a string, and not a time format, so first I converted it using substring as described above by bus_pass_Man

substring(
cast([Time - Emp Activity History]  as varchar(10)) ,1,4 ) || '-'
+
  substring(
cast([Time - Emp Activity History]  as varchar(10)) ,5,2 )|| '-'
||
substring(
cast([Time - Emp Activity History] as varchar(10)) ,7,2 )

but I then get an error when trying to use this to create datetimestamp as mentioned by MFGF, because the resulting string is not a time format and if I try first to cast to time, I receive an error as well.

cast([Date - Emp Activity History] + cast ([Data Item1], time), timestamp)

Show me the full expression you are using. 

[MFGF]

I am wondering why this did not work?  It is essentially the same thing, in the one step rather than two.
cast ([Date - Emp Activity History] + cast([Time - Emp Activity History], Time), timestamp)

Is the syntax wrong.  I got the following error:

RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-126'.
UDA-SQL-0460 A general exception has occurred during local processing.
UDA-EE-0094 The operation "add" is invalid for the following combination of data types: "timestamp2" and "timestamp2"
RSV-SRV-0042 Trace back:

Hi,

It's behaving as though both are timestamps. What do you see if you try:

cast(cast([Date - Emp Activity History], date) + cast([Time - Emp Activity History], time), timestamp)

Cheers!

MF.

[Quigwam2058]

Yes, that does work.  Thanks for clarifying that both expressions had to be cast to the appropriate format to make this work, even though one of them was already a date format.  Appreciate your quick replies!

[MFGF]

Yes, that does work.  Thanks for clarifying that both expressions had to be cast to the appropriate format to make this work, even though one of them was already a date format.  Appreciate your quick replies!

Glad you got things working in the end! Thanks for updating us.

Cheers!

MF.