Formatting datefield which contains timestamp

[#1]

Hi All

I am trying to load only the date from a field which contains date with time stamp. I tried the below code but it is not working as expected. Could you please help me out on this? I have attached the sample application with the qvd file where I am loading from.

Login1:
LOAD Date(Login,'DD/MM/YYYY') as Login
FROM
C:\Login1.qvd(qvd);

Thanks

Attitude

[#2]

Hi All

Below code has done the trick. Is there is anyother alternative for this? If yes please do let me know.

date(floor(num(field)))

Thanks

Attitude

[Miguel_Angel_Baeyens]

Hello,

You can leave the date and floor functions, and that's the correct way to get the results you want.

Date(Floor(Login))

Floor() rounds the number found in Login down. That's needed because you have some timestamps with decimals, meaning not just a date, but a date with hours and minutes -a timestamp- (DD/MM/YYYY hh:mm). When QlikView interprets a numeric value with decimals as a date, you can make it represent only the DD/MM/YYYY but the subjacent value still has the hour:minute information. That's where the floor() function makes sense.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#4]

Hi Miguel

Thanks alot for your support!

Thanks

Attitude

[#5]

Hi Miguel

I used the same thing in the application but it is still giving the date with the timestamp. Please let me know what could be the reason behind that. Your solution did worked previously in different application. Here what could be the reason for that?

For your information when I use Date(Floor(FieldName)) in the script it is not working but whereas when I used the same in the front end it works. May I know how can I do that in the backend please.

Regards

Attitude

[Miguel_Angel_Baeyens]

Hi,

Make sure first that the field has a numeric format, and how it is being read (thousand or decimal separator may avoid Floor() to work).

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[deepakk]

HI Attitude,

Try out this method.

[#8]

Hi Miguel

It is in numeric format only. It is showing like this 31/12/2006 00:00:00

Regards

Attitude

[#9]

Hi Deepak

Excellent code! Thanks... Hope that will solve my problem.

Regards

Attitude

[#10]

Hi Miguel

Previous solution which you gave was working fine only. I did the mistake by giving the alias name which is as same as the field name.

Date(Floor(FieldName)) AS FieldName

When I did the change as below it worked 🙂

Date(Floor(FieldName)) AS FieldName1

Regards

Attitude