Check Month year if exists

Posted on 2015-02-11
Last Modified: 2015-02-17
I have a column called Recevieddate (datetime). I have a string '2/1/2015'. I want to check if month and year exists in Recevieddate when I pass @mydate. Date does not matter.

Sample date

2015-02-01 00:00:00.000
2015-02-06 00:00:00.000
2015-03-05 00:00:00.000
2015-04-15 00:00:00.000

declare @mydate varchar(12)
set@mydate = '2/1/2015'
declare @tf    char(1)

if exists(select *from table1 where receiveddate = @mydate ???)
    set @tf = 'True'
    set @tf = 'false

2/1/2015 should return true because month year exists in Recevieddate
2/15/2015 should return true
Question by:VBdotnet2005
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
LVL 65

Accepted Solution

Jim Horn earned 125 total points
ID: 40604054
What's the purpose of setting @mydate to a varchar, when it contains a date value?
Also, you have @tf char(1), but 'True' and 'False' are more than one character.
Declare @mydate date = '2015-02-01', @tf varchar(10)

IF EXISTS (SELECT receiveddate FROM Table1 WHERE MONTH(receiveddate) = MONTH(@mydate) AND YEAR(receiveddate) = YEAR(@mydate))
   SET @tf = 'True'
   SET @tf = 'False'

Open in new window

LVL 34

Assisted Solution

by:Mike Eghtebas
Mike Eghtebas earned 125 total points
ID: 40604065
if exists (SELECT [receiveddate] FROM Table1 WHERE COALESCE([receiveddate], '19000101') = COALESCE(@mydate, '19000101'))

First part deals with null dates (is not received, has no date yet:

Is null:            returns '19000101'
Is not null:     returns '2/6/2015'   ' for example

The second part deals with the date you supply:

Is null:            returns '19000101'   'highly unlikely
Is not null:     returns '2/10/2015'   ' for example

Now, if they match, record still shows (date exists).
LVL 35

Assisted Solution

by:David Todd
David Todd earned 125 total points
ID: 40604093
Declare @mydate date = '2015-02-01', @tf varchar(10)

IF EXISTS (SELECT 1FROM Table1 WHERE dateadd( month, datediff( month, receiveddate, 0 ), 0 ) = @mydate
   SET @tf = 'True'
   SET @tf = 'False'

Open in new window

LVL 11

Assisted Solution

John_Vidmar earned 125 total points
ID: 40604527
If you have a high-volume table then you do not want to wrap a table-field in a function, you'd lose the ability to use an index (if one existed on that table-field). Instead, the following solution truncates the date-variable to the first-of-the-month (greater than or equal to), and first-of-the-next-month (less than):
	FROM	table1
	WHERE	receiveddate >= REPLACE(STR(MONTH(@mydate)) + '/01/' + STR(YEAR(@mydate)), ' ','')
	AND	receiveddate <  DATEADD(mm,1,REPLACE(STR(MONTH(@mydate)) + '/01/' + STR(YEAR(@mydate)), ' ',''))
		SET @tf = 'True'
		SET @tf = 'False'

Open in new window

LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40613702
vbdotnet2005, do you still need help with this question?

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Database-Scoped Permissions 2 42
Can Selenium do Load Testing? 2 59
Data Analysis 7 59
What does "Between" mean? 6 47
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

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