Simple Value I can not seem to define

[#1]

Hi all,

Firstly thanks for any assistance you can provide.

I have a simple problem (I think it should be) and get resolve it.

I am trying to create a timestamp field that combines the DATE eg. (20/05/2011 00:00:00) with the HOUR (currently a number to represent) and the MINUTE (same format as HOUR)

I have run into a couple of issues.

Firstly I have tried to combine HOUR and MINUTE into TIME - using

time#(num(HOUR&MINUTE), 'hhmm') as TIME
if(Len(TIME)=4 ,Time(Time#(TIME,'hhmm'),'hh:mm') ,  if(Len(TIME)=3 ,Time(Time#('0'&TIME,'hhmm'),'hh:mm') , if(Len(TIME)=2 ,Time(Time#('00'&TIME,'hhmm'),'hh:mm') , if(Len(TIME)=1 ,Time(Time#('000'&TIME,'hhmm'),'hh:mm') , 0 ) ) ) )

to format the TIME correctly, but this is causing problems with MINUTES starting with a 0.

I have used the Floor(DATE) function to realise the date component of the timestamp, but I am at a lose and can not seem to find help on effectively concatonating values together to form a timestamp.

Thanks again

[kji]

try timestamp(floor(DATE) + HOUR/24+MINUTE/1440)

[swuehl]

If DATE is already interpreted as date / timestamp with a numerical representation (you can check this in table view by hovering over the field name), I think it could look like

LOAD

DATE,

HOUR,

MINUTE,

Timestamp(DATE + maketime(HOUR, MINUTE)) as TIMESTAMP

FROM ....;