Timestamp Conversion

[#1]

Hi

Submited By	CreatedDateTime
1270391: S,Sreenath - 09/04/2015 11:42:16 PM#	09/04/2015 11:42:16 PM
SYSTEM - 01/04/2015 01:03:40 PM#	01/04/2015 01:03:40 PM
SYSTEM - 01/04/2015 01:13:27 PM#	01/04/2015 01:13:27 PM
SYSTEM - 01/04/2015 01:28:51 PM#	01/04/2015 01:28:51 PM
SYSTEM - 01/04/2015 01:37:25 PM#	01/04/2015 01:37:25 PM
SYSTEM - 01/04/2015 01:46:16 PM#	01/04/2015 01:46:16 PM
SYSTEM - 01/04/2015 01:49:07 PM#	01/04/2015 01:49:07 PM

Submited by is the Source data. From the source data am creating createdDateTime by using

Left([Submited By],7)as Created_ID,

     time#(TextBetween([Submited By],'-','#'),'YYYY-MM-DD hh:mm:ss')as CreatedDateTime,

Now i have to convert the submited by to 24 Hours Time format.

Thanks

[swuehl · Accepted solution]

Your format code for the timestamp interpretation does not match the string you created using TextBetween(), so you don't get a correct timstamp parsed in.

Try

timestamp(timestamp#(TextBetween('SYSTEM - 01/04/2015 01:03:40 PM#','- ','#'),'DD/MM/YYYY hh:mm:ss TT'),'YYYY-MM-DD hh:mm:ss') as CreatedDateTime

Note that I changed format codes, changed the separator of the Textbetween() function and added a formatting function.

[#3]

Hi,

I am attaching QVW serving the purpose of the requirement. I have generated [Submited By] containing 24H date format.