topic Re: date format in QlikView

date format

Wed, 26 Feb 2014 15:19:54 GMT

Hi there,

I need to create a report and the date field retrieved via my sql DB which is string...I need to convert it date format on qlikview..

My date field is : YYYY-MM-DD h:mm:ss

how can I convert it on qlikview on edit script section?

Thanks for give me a hand!

Re: date format

Wed, 26 Feb 2014 16:14:34 GMT

Hi,

You can use Date#() function.

Example: Date#(Yourfield,'YYYY-MM-DD h:mm:ss')

This would interpret the string as date.

Thanks

Re: date format

DavidFoster1 — Wed, 26 Feb 2014 16:15:55 GMT

In your database, is the field held as a date or text field

Re: Re: date format

Wed, 26 Feb 2014 16:19:11 GMT

Attached is an example.

Thanks

Re: date format

rustyfishbones — Wed, 26 Feb 2014 16:44:24 GMT

Hi,

You can try

Re: date format

Wed, 26 Feb 2014 16:46:29 GMT

You would use the date#() function to tell Qlikview how to interpret the string, and if you want to change the format you would nest it inside of a date() funcion for example:

date(Date#(Yourfield,'YYYY-MM-DD h:mm:ss'), 'MM-DD-YYYY h:mm:ss')

Re: date format

Wed, 26 Feb 2014 16:52:11 GMT

Hi David,

The date field held as a text..

Re: date format

PrashantSangle — Wed, 26 Feb 2014 16:52:37 GMT

hi,

You can use

date(date#(yourDatefield,'YYYY-MM-DD'),'YYYY-MM-DD')

and timestamp(timestamp#(yourDatefield,'h:mm:ss')'h:mm:ss')

if your date field in string in your database.

Re: date format

Wed, 26 Feb 2014 16:56:13 GMT

Hi Ajay,

I have tried it but I have got only one date and I havent seen different dates...I checked DB side and have seen all data...when I retrived data on qlikview I got only current date...what is the wrong for me?

Re: date format

Wed, 26 Feb 2014 17:00:31 GMT

Can you attach your script please if possible. You can may be do a preload like:

Load

Date#(DateField,'YYYY-MM-DD h:mm:ss') as DateField,

Dim1,

Dim2 ;

SQL Select

DateField,

Dim1,

Dim2

From DataBase;