Changing a timestamp to a date on the load script

[#1]

Hi,

I have two tables, changes and SprintCalendar.  Changes has ScheduledStartDate which is a full date/timestamp format.  SprintCalendar has SprintDate which is just the date.  If it were a timestamp, it would be for example, 11/30/2011 00:00:00       To link the fields, I need to convert the Changes.start_date to just the date, as in convertng 11/30/2011 16:30:30 to 11/30/2011 00:00:00.

I have tried DayName, Date#, DayStart, DayEnd, Day, Integer... You name it.  Mostly I get an "Unsupported scalar function:day." error

My script is this: 

SQL SELECT

     start_date as ScheduledStartDate,

     DayStart(start_date) as SprintDate < --  This is where it blows up.  Some functions work, but not the one I want.

FROM changes;

// sprint calendar through 2013

LOAD SPRINTDATE  as SprintDate,

     SPRINTDAYNAME,

     SPRINTWEEK,

     SPRINTCODE

FROM

C:\QV_data\SprintCalendar.xlsx;

Help please - what can I try?  I have searched the webs for any combination of QV, [source system], date, convert, etc. 

Thanks...

[#2]

Are you doing a preceding load before your SQL statement?  The preceding load allows you to use the qlikview functions. For instance:

Load

start_date

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

SQL SELECT

start_date

From Changes;

[#3]

Thanks mz, but it's not working.

I didn't know about using a preceding load. 

I got the single field to load ok, but the rest of the table is missing now

I added another field to see what else came across.

LOAD Date(start_date,'MM/DD/YYYY') as SprintSchedDate;

SQL SELECT

     number as change_number, < -- this field doesn't show up

     start_date   < -- this field doesn't show up either, and I couldn't rename it or else the first LOAD statement didn't work

FROM changes;

LOAD SPRINTDATE  as SprintSchedDate,

     SPRINTDAYNAME,

     SPRINTWEEK,

     SPRINTCODE

FROM

C:\QV_data\SprintCalendar.xlsx;

Ready for the next thing to try... 

< -- this field doesn't show up

[#4]

you need to qualify all of the fields in your select in the preceding load.  You can do a lot with the preceding load I think you will really find it useful.  Example

Load

a,

b,

c;

SQL Select

a,b,c

from table;

[johnw · Accepted solution]

I believe this:

LOAD *
,daystart(ScheduledStartDate) as SprintDate;
SQL SELECT

number as change_number
,start_date as ScheduledStartDate
FROM changes;

But it's hard to avoid all possible errors when just typing in a text window.

[#6]

That's it!  Thanks!

After making all the corrections to re-identify all the field names I genericized for this posting...