Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
how to convert time stamp into date format(e.g.: 2/22/2016 12:00:00 AM) this is the date and time i want date as separate and time as separate ... can you please check it i was give like that but i didn't get the result
date(date#( OrderDate, 'dd/mm/yyyy hh:mm:ss'),'DD-MMM-YYYY') as Date,
time(Time#( OrderDate, 'dd/mm/yyyy hh:mm:ss'),'hh:mm') as time
regards.
Sai Madhu
chk dis
Still i didn't get it can u give any example
Here is a script sample:
LOAD *,
Date(Floor(Timestamp#(OrderDate,'M/D/YYYY hh:mm:ss TT')),'DD-MM-YYYY') as Date,
Time(Frac(Timestamp#(OrderDate,'M/D/YYYY hh:mm:ss TT')),'hh:mm') as Time;
LOAD * INLINE [
OrderDate
2/22/2016 12:00:00 AM
2/28/2016 01:15:45 PM
];
You need to use the correct format code for the interpretation function Timestamp#() or Date#(), note that 'mm' denotes minutes, not month. See the help for a complete overview of the format codes.
Then use Floor() resp. Frac() to only retrieve the integer part of your timestamp value for date resp. decimal places for time.
Use a formatting function Date() resp. Time() to format as you like.
In addition to what Stefan mentioned here you can also set TimeStamp, Date, Time Formats up top so that you don't have to use TimeStamp#, Date#, Time# again and again. Something like this:
SET TimeFormat='hh:mm';
SET DateFormat='DD-MMM-YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss TT'; //Match this with the dates you have right now and make sure to use upper case M for Month (Seems your timestamp may have 2-3 spaces between the date and time so you might have to fix this accordingly.
Once you have done this, you can simply do this using Stefan's example:
LOAD *,
Date(Floor(OrderDate)) as Date,
Time(Frac(OrderDate)) as Time;
LOAD * INLINE [
OrderDate
2/22/2016 12:00:00 AM
2/28/2016 01:15:45 PM
];
But in case you have multiple Timestamps than using the TimeStamp# would make sense.
Sai Madhu, just remember that there are three steps involved:
a) Value interpretation, explicitely or automatic
Automatic Number Interpretation
b) Transformations
[master dimension tables shown here as examples for transformations, but also have a look at the general concept]
c) Value formatting
Hi Sai Madhu,
Also, adding an other point here:
With the latest Data manager, you can make use of the date field conversion to change a field to a date field. This will automatically generate a master calendar for you. A very handy addition.
Hope this helps!
Thanks,
Sangram.
Hi reddy
I got it.