Solved

sql server date format...

Posted on 2015-02-09
18
252 Views
Last Modified: 2015-02-25
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
0
Comment
Question by:gvamsimba
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
  • +3
18 Comments
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40598644
Use:

Convert(varchar(10), cast(DateField), 111)
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 40598648
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
 
LVL 34

Expert Comment

by:ste5an
ID: 40598657
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40598662
correction:

Convert(varchar(10), Cast(DateField As Date), 111)
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40598665
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
 

Author Comment

by:gvamsimba
ID: 40598711
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
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40598765
did you try: Convert(varchar(10), Cast(DateField As Date), 111)

It outputs as string
0
 
LVL 34

Assisted Solution

by:ste5an
ste5an earned 125 total points
ID: 40598767
Okay, I see.

Seems that you can only do some macro magic to do an autoformat on DATETIME columns.
0
 

Author Comment

by:gvamsimba
ID: 40598822
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
 

Author Comment

by:gvamsimba
ID: 40598829
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
 
LVL 34

Accepted Solution

by:
Mike Eghtebas earned 125 total points
ID: 40598892
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
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 125 total points
ID: 40600079
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40600083
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
 

Author Comment

by:gvamsimba
ID: 40600444
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
 
LVL 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 125 total points
ID: 40600485
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
 

Author Comment

by:gvamsimba
ID: 40600514
Agreed Victor..
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40600555
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
 

Author Closing Comment

by:gvamsimba
ID: 40630235
good..
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question