sql server date format...

Hi, I have a View which users connect to via Excel. I have date time colums like below and users
are able to filter on       YEAR, MONTH AND DATE..

11/08/2010 00:00

However now they want the data to be displayed without time it like
11/08/2010 but they still want the excel filter to behave like same i.e filter on YEAR, MONTH AND DATE..

can anyone please let me know how to acheive this ?

Many Thanks
gvamsimbaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Mike EghtebasConnect With a Mentor Database and Application DeveloperCommented:
re:> remains the same...but they will not be able to FILTER in excel on YEAR, MONTH AND DATE..

You can write an Excel macro which runs when the workbook opens to format and recognize that specific column as date.

This macro also could search through all worksheets to locate that certain column automatically if its location is not known in advance.

Mike

post has been revised...

BTW, this will be in addition to Convert(varchar(10), Cast(DateField As Date), 111) to remove time elements.
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Use:

Convert(varchar(10), cast(DateField), 111)
0
 
Steve WalesSenior Database AdministratorCommented:
Have a read of the documentation for the cast and convert functions:

https://msdn.microsoft.com/en-us/library/ms187928.aspx

There are several options available here for displaying a date with just the date (no time) in a format depending upon your local date format display.
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
ste5anSenior DeveloperCommented:
Use a different data type.

SQL Server 2008 introduced the DATE and TIME data types, which do what you want.

Thus use CAST(yourColumn AS DATE) in your views.
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
correction:

Convert(varchar(10), Cast(DateField As Date), 111)
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can convert to DATE data type that only keeps the date and not the time:
SELECT CONVERT(DATE, DateColName)

Open in new window

0
 
gvamsimbaAuthor Commented:
Hi ste5han and Vitor, I have already tried this...but by doing this, the users will not be able to see the data without time but they will not be able to FILTER in excel on YEAR, MONTH AND DATE..

this is the actual issue...

any further thoughts please
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
did you try: Convert(varchar(10), Cast(DateField As Date), 111)

It outputs as string
0
 
ste5anConnect With a Mentor Senior DeveloperCommented:
Okay, I see.

Seems that you can only do some macro magic to do an autoformat on DATETIME columns.
0
 
gvamsimbaAuthor Commented:
Hi eghtebas, but again the issue remains the same...but they will not be able to FILTER in excel on YEAR, MONTH AND DATE..
0
 
gvamsimbaAuthor Commented:
Hi ste5an,

so shall I inform my users that if they have to remove the time in the column, then they will not be able to filter  in excel on YEAR, MONTH AND DATE.. ..and they have to use macros or format the column into date format  ?
0
 
PaulConnect With a Mentor Commented:
In your current view, are you using any convert/cast function on this column?
I assume you are not, and that Excel has detected the column is datetime and simply applied a general format to that data.

I suspect there are 2 possible solutions.
1. Have your users format that column to a display that exclude time (in Excel)
2. use cast(that_field as date) in your view - but this might NOT have ANY affect IN EXCEL

You should not need to apply "a format" in SQL Server because you DO want Excel to understand that column is date information.

----------------
By the way
is:
11/08/2010 00:00

the eleventh day of August 2010
or
the eighth day of November 2010
I rather suspect the answer to this isn't important for this question - but it could have been.
NEVER assume others will understand your date format unless it is an unambiguous date like 23/08/2010
0
 
PaulCommented:
sorry, I just went back over this and you state that casting to DATE does not work because:

" but they will not be able to FILTER in excel on YEAR, MONTH AND DATE.. "

so you did NOTHING except

cast(your_datetime_column as date)

inside your view?

Can we see the complete view sql please?
0
 
gvamsimbaAuthor Commented:
Hi PortletPaul,

Below is the one I used....but no difference, Excel will not give the expected filters without time in it...

The work around for this issue is they can format that column into date and then Excel will remove the time but still keep the filters by YEAR, MONTH AND DAY...

SELECT CONVERT(DATE, DateColName)
0
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
The work around for this issue is they can format that column into date and then Excel will remove the time but still keep the filters by YEAR, MONTH AND DAY...
I don't think is a workaround. I think is how it should be. The column in Excel worksheet should have the same data type as it's returned from the SELECT statement.
0
 
gvamsimbaAuthor Commented:
Agreed Victor..
0
 
PaulCommented:
I too agree,
It is Excel that has added the time to the display format, and thus it is Excel that should be used to hide it.
0
 
gvamsimbaAuthor Commented:
good..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.