Date and time prompt

[#1]

Hi,

I have 2 data bases...MYSQL and oracle. I have extracted data from both these data bases and then created a QVD in which I have joined two attributes using outer join so that I can see all the data. Now I want to use this QVD and generate reports but I am facing a problem in linking the tables date attribute because in MYSQL i have date along with time but in oracle I have only date .

So can any one suggest me how to connect these tables so that I can get only the date.

The data is from QVD so I dont wanna change the QVD by extracting the data again from both the data sources as I takes really long time

Thanks

[Anonymous]

If you want to strip the time part from the MYSQL timestamps you can use floor() to remove the decimal value (which is also the time part of a timestamp) and you will end up with just the date value, like the data from the Oracle source.

[#3]

Hi johannes

Thankyou for the reply

I have done the data extraction from the data base and stored it in QVDs

But the issue that I am facing as per now is when I extract the data from the qvd then it shows time stamp along with date in columns that I extracted from MySQL but only date from Oracle so I wanna this to be rectified instead of changing the data while loading again from the data bases.

[Anonymous]

Hi Sagareddy,

Yes, I follow you. So basically you have:

Timestamp
2011-02-07
2011-02-07 08:10:23
2011-02-07 09:11:01
2011-02-08
2011-02-08 09:35:47
2011-02-08 11:00:51
2011-02-08 16:54:23

And you want to remove the time values and just have dates.

Date(Floor(Timestamp)) as Date should do the trick assuming your Timestamp is formatted in a proper date/time format

[ashfaq_haseeb]

hi Sagareddy

you can even use

date(date#(DateFileld,'YYYY/MM/DD'))

will solve your problem

regards

ASHFAQ