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

x
Solved

# Check Month year if exists

Posted on 2015-02-11
Medium Priority
125 Views
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 ???)
begin
set @tf = 'True'
end
else
begin
set @tf = 'false
end

Sample
2/1/2015 should return true because month year exists in Recevieddate
2/15/2015 should return true
0
Question by:VBdotnet2005
[X]
###### 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

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)

SET @tf = 'True'
ELSE
SET @tf = 'False'
``````
0

LVL 34

Assisted Solution

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).
0

LVL 35

Assisted Solution

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'
ELSE
SET @tf = 'False'
``````
0

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):
``````IF EXISTS
(	SELECT	*
FROM	table1
WHERE	receiveddate >= REPLACE(STR(MONTH(@mydate)) + '/01/' + STR(YEAR(@mydate)), ' ','')
)
BEGIN
SET @tf = 'True'
END
ELSE
BEGIN
SET @tf = 'False'
END
``````
0

LVL 52

Expert Comment

ID: 40613702
vbdotnet2005, do you still need help with this question?
0

## Featured Post

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.
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 : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
###### Suggested Courses
Course of the Month9 days, left to enroll