First and Last Date of Current Month,Previous Month or Current Year in SQL

First and Last Date of Current Month, Previous Month or Current Year in SQL

In my Previous post I have written on how to fetch First and Last Date of Current Month, Previous Month or Current Year in SSRS So, Now in this post. I will tell how to achieve the Same Result in SQL

Below are the queries 

Current Date

select GETDATE()

First date of Current Month or Current month first date

select dateadd(mm,datediff(mm,0,getdate()),0) 

select DATEADD(DAY, -day(DATEADD(mm,-1,getdate())),GETDATE()+1)

Last date of Current Month

select dateadd(day,-datepart(day,getdate()),DATEADD(mm,1,getdate()))

First date of Current Year

 select cast(DATENAME(yy,getdate()) as DATE)

Last date of Current Year

 select dateadd(day,-1,dateadd(mm,12,cast(DATENAME(yy,getdate()) as DATE)))

Previous Month Last Date

select DATEADD(DAY, -day(DATEADD(mm,-1,getdate())),GETDATE())

Previous Month First Date

select dateadd(mm,-1,dateadd(day,1,DATEADD(DAY, -day(DATEADD(mm,-1,getdate())),GETDATE())))

---Other way that are alternate of above all

--Previous month first date

select dateadd(mm,datediff(mm,0,getdate())-1,0)

--Previous month last date

select dateadd(mm,datediff(mm,0,getdate()),-1)

--current year first date

select dateadd(yy,datediff(YY,0,getdate()),0)

--current year last date

select dateadd(yy,datediff(YY,0,getdate())+1,-1)

--current month last date

select dateadd(mm,datediff(mm,0,getdate())+1,-1)

--current month first date

select dateadd(mm,datediff(mm,0,getdate()),0)

Note : There are alternative ways also to fetch the same things differ from logic to logic as below

--First Date and Last Date of Year  you can use this logic also

 SELECT '01/01/'+cast(year(getdate()) as varchar(4)) as [First Day],

 '12/31/'+cast(year(getdate()) as varchar(4)) as [Last Day]

In order to achieve the same output in SSRS read my previous blog

 http://sqlandssrssolutions.blogspot.in/2013/10/first-and-last-date-of-current-month-or.html

First and Last Date of Current Month or Current Year in Report Parameters in SSRS

First and Last Date of Current Month or Current Year in Report Parameters in SSRS

In order to show firstdate and Last Date of Current month or Current Year or Previous Month dates as Report Parameter you have to write below expressions .

First date of Current Month
=Today.AddDays(1-Today.Day)

last date of Current Month
=Today.AddDays(1-Today.Day).AddMonths(1).AddDays(-1)

First date of Current Year
=Today.AddMonths(1-Today.month).AddDays(1-Today.day)

Last date of Current Year
=Today.AddDays(1-Today.Day).AddMonths(13-today.month).AddDays(-1)

Previous Month Last Date
=DateAdd("d",-(Day(today)), Today)

Previous Month First Date
=dateadd(dateinterval.month, -1, today().AddDays(-(today().Day-1)))

SQL Server Product,Version,Edition and Other Information in SQL

SQL Server Product,Version,Edition and Other Information in SQL

Query to Check SQL Server Version Edition ,Product and Other Info Related SQL.

--Version Info

SELECT @@version SQLversioninfo

Output--My SQLServer Info 

SQLversioninfo

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64)   Jun 11 2012 16:41:53   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor) 

--ProductVersion,Productlevel and Edition

SELECT SERVERPROPERTY('productversion') as Productversion , SERVERPROPERTY ('productlevel') Productlevel , SERVERPROPERTY ('edition') edition

Output-My SQLServer Info

Productversion   Productlevel    edition

10.50.2550.0 SP1 Enterprise Edition (64-bit)

--Product,Version,Platform and Other Info

exec xp_msver

Output-My SQLServer Info

Index Name Internal_Value Character_Value

1 ProductName NULL Microsoft SQL Server

2 ProductVersion 655410 10.50.2550.0

3 Language 1033 English (United States)

4 Platform NULL NT x64

5 Comments NULL SQL

6 CompanyName NULL Microsoft Corporation

7 FileDescription NULL SQL Server Windows NT - 64 Bit

8 FileVersion NULL 2009.0100.2550.00 ((KJ_SP1_GDR).120611-1632 )

9 InternalName NULL SQLSERVR

10 LegalCopyright NULL Microsoft Corp. All rights reserved.

11 LegalTrademarks NULL Microsoft SQL Server is a registered trademark of Microsoft Corporation.

12 OriginalFilename NULL SQLSERVR.EXE

13 PrivateBuild NULL NULL

14 SpecialBuild 167116800 NULL

15 WindowsVersion 498139398 6.1 (7601)

16 ProcessorCount 4 4

17 ProcessorActiveMask NULL               f

18 ProcessorType 8664 NULL

19 PhysicalMemory 8000 8000 (8388141056)

20 Product ID NULL NULL

Issue with SSRS Reports type charts Months as Horizontal axis in SSRS

Issue with SSRS Reports type charts Months as Horizontal axis in SSRS

Yesterday while creating a report i found a issue like In my Report i was displaying the Sales pr month

The horizontal axis was showing months name and vertical axis bar as sales per month

while testing the Report i faced a issue.

The Report was not showing all months Name it was showing Months Names every other.

Below is the ScreenShot how Month was coming into Reports. 

If you look into the above screenshot  it is not showing all months Names it is showing bars for each month but not displaying months Name below.

In order to fix this issue.

Go to Design Mode of your Report.

Go to Horizontal Properties--By  Right clicking on Month Names in Design Mode

You will get the below Image.

You will See Interval option in the Image which is by default as Auto change it to 1 and it will fix down the issue.

Preview the Report.

As you will see in the above screenshot all months name are coming .

Searching Column Names in Tables and Views in a Database in SQL

Searching Column Names in Tables and Views in a Database in SQL

Many times you have to Search Some column Names in Entire Database without Script it's very Difficult to get the table names or view Name you have to open Each table or View, then look for the Column you want which is  quite time consuming.

Below are the script to find Column Names in Table 

--To Search a Column Name in all Tables of a Database

SELECT t.name AS table_name,

SCHEMA_NAME(schema_id) AS schema_name,

c.name AS column_name

FROM sys.tables AS t

INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID

WHERE c.name LIKE '%Test%'

ORDER BY schema_name, table_name;

In above script Test is my ColumnName not sure exactly about column name therefore i have used like operator where columnName like '%Test'

It will display TableNames SchemaNames,ColumnName

--To Search a Column Name in all Views of a Database

SELECT t.name AS table_name,

SCHEMA_NAME(schema_id) AS schema_name,

c.name AS column_name

FROM sys.views AS t

INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID

WHERE c.name LIKE '%Test%'

ORDER BY schema_name, table_name;

It will display ViewNames SchemaNames,ColumnName.

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon in SQL.

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

The Reason for this error is when you are calling CTE Comman Table Expression Inside a Batch you have to start the cte with Semicolon otherwise it will throw the above error

To Demonstrate this i will create a table

CREATE TABLE [dbo].[Employee1_Errors](

[Empid] [int] IDENTITY(1,1) NOT NULL,

[EmpName] [nvarchar](50) NULL,

[EmpSalary] [float] NULL,

EmpDob datetime

) ON [PRIMARY]

GO

insert into [Employee1_Errors] values ('vikas',2000,(getdate()-100))

insert into [Employee1_Errors] values ('vikas1',1000,(getdate()-200))

insert into [Employee1_Errors] values ('vikas2',3000,(getdate()-300))

insert into [Employee1_Errors] values ('vikas3',3000,(getdate()-400))

insert into [Employee1_Errors] values ('vikas4',3000,(getdate()-500))

select * from [Employee1_Errors]

Now we will display Result through cte where Salary greater than 2000 and Dateofbirth is less then current Date

declare @date datetime

set @date=GETDATE()

with cte as

( 

select * from [Employee1_Errors]

)

select * from cte where empdob <@date and [EmpSalary]>=2000

Msg 319, Level 15, State 1, Line 3

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

The Reason for this Error is we are using CTE in between the batch so in order to remove error we have to terminate previous Statement by semicolon or start CTE with semicolon

declare @date datetime

set @date=GETDATE()

;with cte as

( 

select * from [Employee1_Errors]

)

select * from cte where empdob <@date and [EmpSalary]>=2000

This will Display the output without any Error

Note:It's always a good practise whenver you are using CTE always start it will Semi colon so that you can avoid these type of errors while working with CTE

Error Subreport could not be shown in SSRS

Error Subreport could not be shown in SSRS Issue Reason and Solution

Error SubReport could not be shown -The Main cause of this error is the SubReport Parameters Name have some difference with Main Report Parameters Name. 

Below are points to Keep in Mind while Passing Parameter from Main Report to Sub Report.

1-The Name data type of Main Reports Parameter should be same as SubReport.

2-SSRS is Case Sensitive the error is Mainly caused when your CaseParameters  of Main Report and Sub Reports are Different make sure not only the Parameter Name but also the case of Paramaters should be same.

one More Cause of this Issue which i faced I will explain you by example

I created a Main Report and Sub Report Passing Parameters from Main Report to Sub Report

with Same Name ,datatype and case also but still that error was there.

I Preview the Sub Report  separetly it was working fine

for 2 hours i was not to resolve the issue when everything is fine.

Then again i checked my Main Report

In the Main Report I select my Sub Report 

Right click-Properties -Parameters

I checked that i was manually writing the Main Reports Parameters Name instead of Selecting it from Parameters list

So I clicked on Fx 

Select the Parameter from Parameter List Did the Same for all parameters 

Preview the Report.

Finally i was able to Resolved the Issue.

3-So Keep in mind that instead of writing Parameters Name Manually it's better to Select Parameter Names from Parameters list.