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

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

saivina2920 — Sat, 16 Nov 2024 04:07:04 GMT

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.

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

saivina2920 — Wed, 20 Mar 2019 01:39:58 GMT

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.

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

prabir_c — Wed, 20 Mar 2019 06:02:16 GMT

Hi Saivina,

Please check attached file

Hope it helps!

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

saivina2920 — Wed, 20 Mar 2019 07:21:24 GMT

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

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

saivina2920 — Wed, 20 Mar 2019 07:54:33 GMT

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

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

prabir_c — Wed, 20 Mar 2019 09:18:50 GMT

Hi Saivina,

Try This.. May be it work..

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

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

saivina2920 — Wed, 20 Mar 2019 09:24:53 GMT

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]