topic Date format for SQL server data in QlikView

Date format for SQL server data

Mon, 24 Jan 2011 08:48:10 GMT

Hi,

I am reading in data from a SQL server and I the one field is in the format yyyy.dd.mm. How can I format this in QV into a date format with the format yyyy/mm/dd? Then function Date(Datevariable, 'YYYY/MM/DD') As Date does not seem to work in SQL syntax.

It will be brilliant if anyone can help me!

Date format for SQL server data

deepakk — Mon, 24 Jan 2011 09:59:33 GMT

Hi,

Use Load statement before the sql and then use the Date function.

eg:

Load

Date(Date,'YYYY/MM/DD') as Date;

select Date from Table;

Date format for SQL server data

Mon, 24 Jan 2011 10:11:44 GMT

Thanks Deepak but do I understand you correctly that after the Connect to the sql server code I must insert the load statement and then in the select statement I must select the date?

Date format for SQL server data

Mon, 24 Jan 2011 11:00:44 GMT

Hi,

Ya, You have understood properly.

Your Code Should be as follows.

//QV Load Statement, Here you are converting the SQL date in to proper Date using "Date()"

Load Field1,

Field2,

Date(Date_Field,'YYYY/MM/DD') as Date;

//SQL server Select Statement, Here you are Taking the data from SQL Server.

Select Field1,

Field2,

Date

From Table;

Note: Your "Load" stateme should be above "SQL" Statement.

Hope this helps you.

- Sridhar

Date format for SQL server data

Mon, 24 Jan 2011 13:12:34 GMT

Thanks guys that sorted the problem!

Date format for SQL server data

Fri, 02 Sep 2011 22:23:19 GMT

if you need to then filter by that date how do you do it i.e. SQL Select * where Date >= '08/25/2011' ? I cannot get this to work

Re: Date format for SQL server data

Fri, 04 Jan 2013 05:59:04 GMT

Hi,

Use the below script:

Table1:

LOAD Field1,

Field2,

Date(Date_Field,'YYYY/MM/DD') as Date;

SQL SELECT

Field1,

Field2,

Date(Date_Field,'YYYY/MM/DD') as Date;

FROM Table;

Regards,

Snehal Nabar