how to Split DateTime value in seprate date and time column

Question

how to Split DateTime value in seprate date and time column

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

dan.hare (7/30/2009)
Agreed that this should be done in a view or calculated column.
One reason why one would want to split out date and time is to allow the use of separate date and time dimensions in Analysis Services to give intra day analysis, eg what time of day do we receive the most calls ?

I thought that Analysis Services was supposed to be the bee's-knees... it doesn't do this without have to split dates and times? :blink:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

dan.hare SSC Veteran Points: 286 More actions · Answer 2

I may be wrong but have seen this approach recommended by SSAS luminaries.

It is indeed possible in Analysis Services to do this with one single date/time combined dimension or with MDX but in order to be able to browse by different date and time granularities without having a massively huge date/time dimension with all possible permutations this is one of the more elegant ways of doing it.

How would you select otherwise orders on the first of the month between 9 and 10 am ?

Summed by quarter. or half. or year. or day of week.

Interactively by the user.

Fancy it in T-SQL ? = )

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

In all the SSAS recommendations I've read (and the ones I've made) the datetime is stored in the SQL table as a datetime, to whatever granularity the user wants to be able to slicetheir cube. If they want to a day (fairly normal) then no time at all. If they want to the minute (very unusual) then clean off the seconds as part of the import into the star schema. Then SSAS uses a time dimension table to do the aggregations.

Which SSAS people recommend splitting date and time? I can't offhand think why, it'll make the process of building the cube harder.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

GilaMonster (7/30/2009)
In all the SSAS recommendations I've read (and the ones I've made) the datetime is stored in the SQL table as a datetime, to whatever granularity the user wants to be able to slicetheir cube. If they want to a day (fairly normal) then no time at all. If they want to the minute (very unusual) then clean off the seconds as part of the import into the star schema. Then SSAS uses a time dimension table to do the aggregations.
Which SSAS people recommend splitting date and time? I can't offhand think why, it'll make the process of building the cube harder.

That's more like it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

dan.hare SSC Veteran Points: 286 More actions · Answer 5

I didn't explain myself well enough, apologies.

Of course you can choose to report a date/time to whatever level of granularity you want.

Dead easy.

My point was that if you choose to look for patterns in the time of day that something happens irrespective of which day it was and still be able to aggregate across days/weeks/months/years then I believe you need separate dimensions for date and time of day.

I am doing a project where I think that this is required for resource planning purposes, eg we need more people to be attending the correction queues between 9 and 11 am on Mondays.

This recommendation was given by Chris Webb and Allan Mitchell at a pre conference session I attended at PASS Europe this year.

If I misunderstood it (and I would be delighted to be corrected for the reason above ! ) then the fault is of course all mine and not theirs ! 🙂

hth

kshama.v.gojamgunde SSC Rookie Points: 33 More actions · Answer 6

thanks

i want to split date from date time and your solution help me.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

dan.hare (7/30/2009)
I didn't explain myself well enough, apologies.
Of course you can choose to report a date/time to whatever level of granularity you want.
Dead easy.
My point was that if you choose to look for patterns in the time of day that something happens irrespective of which day it was and still be able to aggregate across days/weeks/months/years then I believe you need separate dimensions for date and time of day.
I am doing a project where I think that this is required for resource planning purposes, eg we need more people to be attending the correction queues between 9 and 11 am on Mondays.
This recommendation was given by Chris Webb and Allan Mitchell at a pre conference session I attended at PASS Europe this year.
If I misunderstood it (and I would be delighted to be corrected for the reason above ! ) then the fault is of course all mine and not theirs ! 🙂
hth

Heh... so what? No need to drop names... Even the best of the best may recommend something that's not right for a given situation. "It Depends".

I agree that it would be nice to have separate values for the type of analysis you speak of but there are a lot of other things to be done that include both values as a single single date/time data type. The real key is that you need it both ways... as a single value and as two separate values... for most folks, I recommend storing the single date/time value and using computed columns to do the split so that one might easily enjoy the best of both worlds.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions