Problem with trying to combine Date and Time fields. Any help please?

[#1]

Hello there,

I am trying to combine a date and time field together into one:

Job_Date	Job_Time
01/01/2015	00:11:23
04/01/2014	23:45:21
16/01/2015	10:21:33

I tried using:

timestamp#(Job_Date & ' ' & Job_Time,'DD/MM/YYYY hh:mm:ss') to get both of them in one field but this function seems to convert the date into numbers and keeps the time format strangely.

Any idea how I can get around this please?

[#2]

Sorry what I meant was:

If I use timestamp(Date) then it gives me something like 01/01/2015 00:00:00.

When I do timestamp(Date+Time) it then gives me blanks even though there are time values in the Time field.

This is my issue

There are some blanks in both the Date and Time fields.

[jonasheisterkam]

Is in youre Time realy only a time value or is it only the shown format. Can you upload a qvw with the 2 fields?

Blanks can removed with replace or purgechar or with keepchar you can remove all but signs you expect.

[#4]

Hi Jonas,

Here is the attached QVW file with the date and time!

[rwunderlich]

Your Time field is not being loaded as a Time (fraction). I suggest reviewing these two blog posts before going much further. Mis-understanding QV Date/Times can send you down a rabbit hole.

Why don’t my dates work?

The Date Function

Note that in my opinion, any recommendations that look like:

    =Date(myTimeStamp)

    =Time(myTimeStamp)

are generally incorrect and will take you into the aforementioned rabbit hole.

-Rob

[MarcoWedel]

Maybe your timefield is loaded as Text, not as dual (text with underlying numerical value).

You could check in the table viewer or using the expression

num(Job_Time)

hope this helps

regards

Marco

[#7]

Hi Rob,

So what shall I do to the fields to fix the underlying issues? Do you have some practical guide sorry I am new to this

I have to do analysis on the dates and it's becoming complicated with these issues!

[#8]

Hi Marco,

How can I load my data as dual?

[jonasheisterkam]

Use the following in the load.

time(time#(Time,'hh:mm:ss')) as Time

If it appears in your Tablebox after load on the right side of the cells it's a time()

[MarcoWedel]

Another interesting post :

Get the Dates Right

regards

Marco