How to convert required date format from this date (02-11-19 04:45:25.089000000 PM)

[narayanaqlik]

Hi All, I am getting data or Dates from DB the mentioned format when i am using Date(Date#(CreatedDate),'DD/MM/YY') then i am getting blank field Kindly help in this how to convert into required date formats like Year, Quarter, Month,Weekday, and days Here i am facing some issues to convert into required dates: 1)for example i am converting these date using date(left(CreatedDate),'DD/MM/YY') then i am getting year as date(DD), and date as Year(YY) EX:02-11-19 04:45:25.089000000 PM i am getting 19/11/2002 which wrong. 2) if i am using same converted dates(19/11/2002) in Bar chart or line chart, by default it is showing same dates but whenever i am selecting any date then it will be displayed all timestamps for that dates but i don't want those timestamps i.e it is not converting complete require date format. for Ex: if i am clicking any date in a particular month then it will display only that month not the all detailed level dates(original dates) Kindly help in this...! Thanks every one in advance...! CreatedDate 02-11-19 04:45:25.089000000 PM 02-11-19 04:48:39.622000000 PM 02-11-19 04:56:58.323000000 PM 02-11-19 04:58:12.430000000 PM 02-11-19 04:59:27.343000000 PM 02-11-19 05:06:09.353000000 PM 03-11-19 04:24:34.243000000 PM 03-11-19 05:03:55.702000000 PM 03-11-19 05:15:55.635000000 PM 03-11-19 05:20:55.799000000 PM 04-11-19 02:19:33.451000000 PM 05-11-19 05:51:42.373000000 PM 06-11-19 04:12:55.420000000 PM 06-11-19 05:14:45.312000000 PM 07-11-19 10:21:36.638000000 PM 09-11-19 03:11:22.548000000 PM 09-11-19 03:14:17.989000000 PM 09-11-19 03:14:30.303000000 PM 09-11-19 12:44:34.693000000 PM 09-11-19 12:45:58.256000000 PM 09-11-19 12:46:00.318000000 PM 09-11-19 12:46:00.946000000 PM 09-11-19 12:46:01.457000000 PM 09-11-19 12:46:04.194000000 PM 11-11-19 08:25:24.880000000 PM 11-11-19 08:38:10.627000000 PM 11-11-19 08:38:21.523000000 PM 11-11-19 08:39:34.530000000 PM 11-11-19 08:40:25.739000000 PM 11-11-19 08:40:33.574000000 PM 11-11-19 08:43:40.294000000 PM 11-11-19 08:44:42.594000000 PM 11-11-19 08:44:49.456000000 PM 11-11-19 08:45:45.993000000 PM 11-11-19 08:46:15.003000000 PM 11-11-19 08:48:19.718000000 PM 11-11-19 08:48:26.224000000 PM 11-11-19 08:50:56.935000000 PM 12-11-19 12:02:43.191000000 PM 12-11-19 12:43:40.640000000 PM 12-11-19 12:43:40.641000000 PM 13-11-19 02:00:45.635000000 PM 13-11-19 02:32:10.985000000 PM 13-11-19 02:39:35.331000000 PM 13-11-19 02:41:07.443000000 PM 13-11-19 03:22:36.907000000 PM 13-11-19 03:39:06.013000000 PM 13-11-19 03:40:28.961000000 PM 13-11-19 06:01:16.506000000 PM 15-11-19 06:37:16.005000000 PM 15-11-19 06:37:41.129000000 PM 15-11-19 06:45:15.778000000 PM 15-11-19 06:58:00.210000000 PM 15-11-19 07:26:20.991000000 PM 15-11-19 07:32:58.306000000 PM 15-11-19 11:22:43.956000000 AM 19-11-19 11:35:44.048000000 AM 26-10-19 03:14:50.061000000 PM 26-10-19 03:17:20.524000000 PM 26-10-19 03:40:24.709000000 PM 26-10-19 03:43:27.820000000 PM 26-10-19 03:46:43.873000000 PM

[Yousef_Amarneh]

Load Date(Date#(Left(CreatedDate,8),'DD-MM-YY')) as Date,

           Year(Date(Date#(Left(CreatedDate,8),'DD-MM-YY'))) as Year,

           Month(Date(Date#(Left(CreatedDate,8),'DD-MM-YY'))) as Month,

           'Q' & Ceil(Num(Month(Date(Date#(Left(CreatedDate,8),'DD-MM-YY'))))/3) as Quarter,

           Weekday(Date(Date#(Left(CreatedDate,8),'DD-MM-YY'))) as Weekday

[sunny_talwar · Accepted solution]

You can also use TimeStamp#() function to help Qlik interpret your date and then do transformations on top of it. Example

Date(Floor(TimeStamp#('02-11-19 04:45:25.089000000 PM', 'DD-MM-YY hh:mm:ss.fff TT')))

In the script

Date(Floor(TimeStamp#(FieldName, 'DD-MM-YY hh:mm:ss.fff TT'))) as Date

[narayanaqlik]

Thanks Sunny  and Umaresh for your quick response. it is working fine

[narayanaqlik]

Sorry to say Amarneh 

It is not working 

In this way that fields are blanks only it will not generating dates 

[narayanaqlik]

Hi Amareh Thanks, it is also working fine, any one can use this way also thanks a lot both of you