Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

saivina2920
Contributor

How to convert string to date and get count specified period FROM TODAY..

How to convert string to date and get count specified period FROM TODAY..


I'm using Select statement which is connected to SQL Server.

My field Column Name is "EmpJoinDate" as varchar in SQL table.

I just want to convert into date format or directly get count for the specified period FROM TODAY..


I want to get the below range count from today's date. How to write the Expression


<5days    (count)         

 

5 to 10 Days      (count)       

 

> 15 DAYS  (count)

Sample Files Attached for reference

Note : I'm Using SELECT SQL queries from SQL Server not LOAD Statement.

 

Labels (3)
1 Solution

Accepted Solutions
Partner
Partner

Re: How to convert string to date and get count specified period FROM TODAY..

Hi Saivina,

Try This.. May be it work..

Date(Floor(Date#(EmpJoinDate,'DDMMMYYYY:hh:mm:ss')), 'DD/MM/YYYY') as EmpJoinDate

6 Replies
saivina2920
Contributor

Re: How to convert string to date and get count specified period FROM TODAY..

can anyone suggest how to form the Expression  using sample file.

How to convert SQL varchar to date and process it

this is required as soon as possible to implement my projects.

Highlighted
Partner
Partner

Re: How to convert string to date and get count specified period FROM TODAY..

Hi Saivina,

Please check attached file

Hope it helps!

saivina2920
Contributor

Re: How to convert string to date and get count specified period FROM TODAY..

Excellent.. i have one queries.
I'm using SQL Server as Back End and my Query is SQL SELECT Statement not LOAD from EXCEL.
In the Reference attached excel file, we are converting varchar to date.
The same way, How do i convert SQL varchar fields to date fields in Script page.
Example below..
LOAD * ,
NewDate ,
Date(Date#(Dates,'DDMMMYYYY:hh:mm:ss'),'DD-MM-YYYY') as NewDate
;

Instead of above, Shall i use below .....??????????/
OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=False.............]

SQL SELECT [EmpJoinDate],
Date(Date#(EmpJoinDate,'DDMMMYYYY:hh:mm:ss'),'DD-MM-YYYY') as EmpJoinDate ==> Will this Work in SQL SELECT Statement.....???



Tags (1)
saivina2920
Contributor

Re: How to convert string to date and get count specified period FROM TODAY..

any update pls.

SQL SELECT [EmpJoinDate],
Date(Date#(EmpJoinDate,'DDMMMYYYY:hh:mm:ss'),'DD-MM-YYYY') as EmpJoinDate ==> Will this Work in SQL SELECT Statement.....???
Partner
Partner

Re: How to convert string to date and get count specified period FROM TODAY..

Hi Saivina,

Try This.. May be it work..

Date(Floor(Date#(EmpJoinDate,'DDMMMYYYY:hh:mm:ss')), 'DD/MM/YYYY') as EmpJoinDate

saivina2920
Contributor

Re: How to convert string to date and get count specified period FROM TODAY..

Sorry.  getting Error. 

Error : 

ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: 'Date#' is not a recognized built-in function name.
SQL SELECT [EmpJoinDate], Date(Floor(Date#([EmpJoinDate],'DDMMMYYYY:hh:mm:ss')), 'DD/MM/YYYY') as [EmpJoinDate Formatted]