replacing a sql function in a load script to convert date

[#1]

I have a sql function that converts a datetime field to provide me MM-DD, the function is below:

CREATE FUNCTION func_convert_date( @in_date DATETIME )

RETURNS NVARCHAR(255)

AS

BEGIN

      DECLARE @str_date nvarchar(255);

      SET @str_date = ( SELECT RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(MONTH, @in_date)), 2) + '-' + RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(DAY, @in_date)), 2) );

      RETURN @str_date;

END;

Im trying to apply this to a load script, or find an alternative method to convert a datetime into MM-DD. Is there a way to do this in the load script?

Thanks in advance

[jaimeaguilar]

Hi,

the equivalent in QlikView script would be something like

Date(Date#(DateField, ' Put here the original format of the date field eg. YYYYMMDD, DD/MM/YYYY '), ' Put here the desired format, in this case MM-DD ')

so the code would be:

Load

Field1,

Field2,

Date(Date#(DateField, 'YYYYMMDD'), 'MM-DD') as DateName;

SQL Select *

FROM Table;

Regards

[jagan]

Hi,

Try like thi

Data:

Load

*,

Date(Date#(DateDimensionName, 'DateFormat'), 'MM-DD') as Date;

SQL Select *

FROM Table;

Replace DateDimensionName with your date field, and DateFormat with the format specifiers of your datefield (MM/DD/YYYY etc).

Regards,

Jagan.

[#4]

Thanks guys! this 'almost' works.. The reload of the script runs, I see the field but I get an empty list box. All the other fields from the table appear, so Im sure theres just one small thing Im missing in this function. My load statement is below.

Data:

Load

*,

Date(Date#(BIRTHDATE, 'YYYY-MM-DD'), 'MM-DD') as Birthday;

SQL Select *

FROM  TABLE;

The BIRTHDATE field is DATETIME format, would that make a difference?

Regards,

Grant

[#5]

I got it guys! Floor worked like a charm

Data:

Load

*,

Date( Floor( BIRTHDATE ), 'MM-DD') as Birthday;

SQL Select *

FROM  TABLE;

Thanks for your help, shot me in the right direction

Regards,

Grant