topic Re: Timestamp Conversion in QlikView

Timestamp Conversion

Mon, 22 Jun 2015 09:27:41 GMT

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

Re: Timestamp Conversion

swuehl — Mon, 22 Jun 2015 09:37:31 GMT

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.

Re: Timestamp Conversion

Mon, 22 Jun 2015 11:47:53 GMT

Hi,

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