Solved

sql server date format...

Posted on 2015-02-09
18
248 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40598662
correction:

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

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 50

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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

734 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