Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

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
Highlighted
Partner
Partner

Hi Saivina,

Try This.. May be it work..

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

View solution in original post

6 Replies
Highlighted
Creator
Creator

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

Hi Saivina,

Please check attached file

Hope it helps!

Highlighted
Creator
Creator

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.....???



Highlighted
Creator
Creator

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.....???
Highlighted
Partner
Partner

Hi Saivina,

Try This.. May be it work..

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

View solution in original post

Highlighted
Creator
Creator

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]