Solved

sql server date format...

Posted on 2015-02-09
18
231 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
  • 6
  • 4
  • 3
  • +3
18 Comments
 
LVL 33

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 32

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
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40598662
correction:

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

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 33

Expert Comment

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

It outputs as string
0
 
LVL 32

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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

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 33

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 48

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 48

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 45

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 48

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now