Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Check Month year if exists

Posted on 2015-02-11
Medium Priority
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 66

Accepted Solution

Jim Horn earned 500 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 500 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 500 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 500 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 52

Expert Comment

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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : All lightning effects with instructions : http://www.mediaf…

721 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