How to remove timestamp from date field?

[ry1633]

Hello,

I may have asked this before and if so I apologize because I couldn't find the answer.   I have a some date fields in my model that come in with a timestamp looking something like this:  '10/29/2013 8:31:34 AM'

My users would like the timestamp trimmed off so only the date displays.  Is there way to do this in Framework Manager - or is this something I should have my DBA guys do on their end?    A caveat also:   not every date in the field has the timestamp on it, some do and some do not.

[bdbits]

If they always want the time dropped - no exceptions - I would cast the column to a date at ETL time.

If it is mixed usage - sometimes the time portion matters - I would probably create a secondary column with the date but not the time. You could format the datetime to now show the time, but it would still be taken into account, e.g. in filters.

[Lynn]

Is the field a date/time data type? Hopefully it isn't a varchar or string of some sort.

You can try casting to a date data type which will eliminate the time stamp portion. I'm not sure I understand what you mean when you say some have the time stamp and some do not. For the ones that do not I'm assuming they just show 00:00 which is midnight. Unless it is a string and not really a date/time data type. As far as I know a date/time is going to have a time stamp no matter what.

Code Select Expand


cast ( [Your Item], DATE )


There is also a default data format you can set, but that only affects the display of the information I think. It would still group and summarize around the date/time rather than just around the date.

It is generally better if the DBAs could do this to avoid the extra processing needed to execute that function but if it doesn't introduce a performance problem then doing it in the model is ok. I would suggest including the field as it is with both date and time, and then adding a field with only the date portion. Sometimes it also makes sense to include a third item with just the time portion in case users might want that all by itself. This makes things very drag-n-drop easy for authors when various formats are needed.

Oh snap...bdbits got in there with similar suggestions.

[ry1633]

I think the users want it removed.   

What I meant is that within the same column of data - this example is from one called "FIRST_ISSUE_DATE"  there are values with and without the timestamp like this:

11/27/2012
3/27/2013
3/27/2013
3/28/2013
10/29/2013 8:31:34 AM

[bdbits]

lol Lynn - well, great minds think alike!  And yours was much better in presentation than my relatively curt reply.  8)

ry1633 as we both said albeit differently, it depends on whether the users ever care about the time. If not, have it dropped in the database. Otherwise, create a secondary column with just the date and use that for things like prompts, filters, etc.

[Lynn]

I think the users want it removed.   

What I meant is that within the same column of data - this example is from one called "FIRST_ISSUE_DATE"  there are values with and without the timestamp like this:

11/27/2012
3/27/2013
3/27/2013
3/28/2013
10/29/2013 8:31:34 AM

What is the data type of the field?? Is it a date/time? A varchar? A char? Did you try casting it to a date as both Bob and I suggested?

[ry1633]

How do I put in the CAST statement?   Do I highlight the field in the query subject and put it under the Calculation tab?

[Lynn]

You can modify the expression for that item in your model query subject. Or add a new query item to your model query subject and do it there if you want to preserve the original and add a new item for the modified one. It is not a calculation.

[ry1633]

Here is a screen shot.   When I click the field (in this case is FIRST_ISSUE_DATE), the only comparable option it gives me is "Edit Definition" and it takes me to this screen.     Where the only option I can see there is to add it as a calculation.   

It gives me no option to modify the expression for the field itself.

[MFGF]

Here is a screen shot.   When I click the field (in this case is FIRST_ISSUE_DATE), the only comparable option it gives me is "Edit Definition" and it takes me to this screen.     Where the only option I can see there is to add it as a calculation.   

It gives me no option to modify the expression for the field itself.

That's because the image you posted is for a Data Source query subject. Lynn pointed you to making this modification in a Model query subject - she even made the word "model" bold to draw your attention to it. You're looking in the wrong place. Go to the query subjects in the next layer above your data source query subjects and make the change there.

MF.