SQLServerCentral.com / Reporting Services / Reporting Services / Displaying dates in Name and dd-mm-yyyy / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 02 Aug 2015 15:17:05 GMT20RE: Displaying dates in Name and dd-mm-yyyyhttp://www.sqlservercentral.com/Forums/Topic855082-150-1.aspxHi Willem, i tried on this queryselect x.TRUSTID,Y.Date1, isnull(x.total,0) from (select TRUSTID, Date=convert(varchar(25),UPLOADTIME,103),Total=SUM(uploadlncnt) from DCLIVEJOBS where UPLOADTIME between '2010-01-01' and '2010-01-31'group by TRUSTID,UPLOADTIME)Xright outer join(select '' as TA_A_NAme,convert(varchar(25),DATE,103) as date1,0 as Total from dbo.F_TABLE_DATE('01-Jan-2010','31-Jan-2010'))Y on Y.Date1=X.Date order by Y.Date1but i am getting 0 below the date column with a new row added (attached the screenshot), and more over i need to add a new row below the date date row and display the date as 31-Jan-2000Tue, 02 Feb 2010 06:02:01 GMTjaype-365633RE: Displaying dates in Name and dd-mm-yyyyhttp://www.sqlservercentral.com/Forums/Topic855082-150-1.aspxYou need to do an outer join with a table that contains all dates on which you want to report.Check this [url=http://www.sqlservercentral.com/Forums/Topic857563-392-1.aspx]topic[/url] for some inspiration.Tue, 02 Feb 2010 04:53:18 GMTwschampheleerRE: Displaying dates in Name and dd-mm-yyyyhttp://www.sqlservercentral.com/Forums/Topic855082-150-1.aspxHi Willem, thanks its working fine now, i have used=DateSerial(Fields!Year.Value-10, Fields!Month.Value+3,Fields!Date.Value-1) in Expression on that column and in same column text box properties &gt; Number &gt; in category i chose date and in type 31-Jan-2000, but now only thing is i am not getting all the dates of the month january, coz there are no data's in 1st,2nd and 3rd date, i need to display whole of jan month with date's and in the data column as 0.do i need to change the query, if yes, can you please help on the query Thanks,JprakashTue, 02 Feb 2010 04:28:03 GMTjaype-365633RE: Displaying dates in Name and dd-mm-yyyyhttp://www.sqlservercentral.com/Forums/Topic855082-150-1.aspxWhy are you still using datepart when you have already done the extraction in your query?Try this:=DateSerial(Fields!Year.Value-10, Fields!Month.Value+3,Fields!Date.Value-1)Fri, 29 Jan 2010 13:04:15 GMTwschampheleerRE: Displaying dates in Name and dd-mm-yyyyhttp://www.sqlservercentral.com/Forums/Topic855082-150-1.aspxIf try something simple, it might make things easier and less errors,If you could return the CHECKINTIME, just go the the Text Box Properties, in the Number/Custom section, using Custom Format:dddd, will show name of weekday, like Monday, Tuesday...d-MMM-yyyy, will show format of 0-Jan-2010... Fri, 29 Jan 2010 10:20:49 GMTwweiRE: Displaying dates in Name and dd-mm-yyyyhttp://www.sqlservercentral.com/Forums/Topic855082-150-1.aspxHi Willem, attached the screenshotThe error i am getting is in reports as #Error in the day number which was displaying earlier, and in reports on expression i have used =DateSerial(DatePart("YYYY",Fields!Year.Value)-10, DatePart("m",Fields!Month.Value)+3,DatePart("d",Fields!Date.Value)-1)Fri, 29 Jan 2010 08:26:55 GMTjaype-365633RE: Displaying dates in Name and dd-mm-yyyyhttp://www.sqlservercentral.com/Forums/Topic855082-150-1.aspxThere's a few things to be said about your query but syntactically it is correct.Where are you getting an error and what does it say?Fri, 29 Jan 2010 07:42:14 GMTwschampheleerRE: Displaying dates in Name and dd-mm-yyyyhttp://www.sqlservercentral.com/Forums/Topic855082-150-1.aspxHi Willem, the query i have used in sql isSELECT DATEPART(YY, CHECKINTIME) AS Year, DATEPART(MM, CHECKINTIME) AS Month, DATEPART(D, CHECKINTIME) AS 'Date', SUM(UPLOADLNCNT) AS 'Total Lines', TRUSTIDFROM DEPTABLEGROUP BY DATEPART(yy, CHECKINTIME), DATEPART(M, CHECKINTIME), DATEPART(D, CHECKINTIME), TRUSTIDORDER BY Year DESC, Month DESC, 'Date' DESCand i tried the examples, but still i am getting error.Please help me on thisFri, 29 Jan 2010 07:22:27 GMTjaype-365633RE: Displaying dates in Name and dd-mm-yyyyhttp://www.sqlservercentral.com/Forums/Topic855082-150-1.aspxFor converting the number to a date, you can use the DateSerial function:DescriptionReturns a Date value representing a specified year, month, and day, with the time information set to midnight (00:00:00).Examples=DateSerial(DatePart("yyyy",Fields!BirthDate.Value)-10, DatePart("m",Fields!BirthDate.Value)+3,DatePart("d",Fields!BirthDate.Value)-1)=DateSerial(DatePart(DateInterval.Year,Fields!BirthDate.Value)-10,DatePart("m",Fields!BirthDate.Value)+3,DatePart("d",Fields!BirthDate.Value)-1)=DateSerial(2007,10,24)To get the name of the day, use the following Expression=weekdayname(weekday(now()))where you replace now() with your date(time) field.Thu, 28 Jan 2010 05:14:24 GMTwschampheleerDisplaying dates in Name and dd-mm-yyyyhttp://www.sqlservercentral.com/Forums/Topic855082-150-1.aspxHi All, I need a help on displaying dates in Name and dd-mm-yyyy on the particular month, when there is no data on the date, the dates are not coming in reports. attached the screen shot of what i am getting from RS and what i am expecting, please give me your valuable advice on how to get the information.ThanksJprakashThu, 28 Jan 2010 04:53:11 GMTjaype-365633