Date format for SQL server data

[#1]

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!

[deepakk]

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;

[#3]

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?

[#4]

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

[#5]

Thanks guys that sorted the problem!

[#6]

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

[#7]

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