compare calendar date to date in sql server db? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

compare calendar date to date in sql server db?

Author

Topic

KarinElvira
Starting Member

47 Posts

Posted - 2011-01-16 : 20:54:00

I want to compare a calendar date of this format '2011-01-18' to dates in sql server db. In db I have date+time (smallDateTime) of this format '2011-01-18 12:00:00'. How can I compare just this part '2011-01-18' of the db date+time to the calendar date? I don't mind changing the format of the calendar date if that would make things easier.
Thankful for help!

kashyap.2000
Starting Member

22 Posts

Posted - 2011-01-16 : 21:13:22

DECLARE @d1 DATETIME
SET @d1='2011-01-18'

DECLARE @d2 DATETIME
SET @d2='2011-01-18 12:00:00'

--Converting both dates to same format

IF (CONVERT(VARCHAR(50),@d1,101) =CONVERT(VARCHAR(50),@d2,101))
BEGIN
SELECT 'Equal'
END
ELSE
BEGIN
SELECT 'Not Equal'
END

Failures will either break you or will make you a better perosn....

KarinElvira
Starting Member

47 Posts

Posted - 2011-01-17 : 04:08:28

Hmm... that's a bit above my level, I think. I get the date like this:
string date = CalendarBokadeMoten.SelectedDate;
but can't find varchar as an option to convert to, only char.
My sql statement looks like this:
string sql = "SELECT Meetings.ID, Meetings.subject, Meetings.description, Occasion.dateStart, Occasion.dateStop FROM Meetings"
+ " JOIN Occasion ON Meetings.ID = Occasion.meetingID WHERE Meetings.status = 'bokad' AND CONVERT(VARCHAR(50), Occasion.dateStart, 101) = @date";

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-01-17 : 04:44:16

Select * from <yourtable>
where convert(varchar(10),'2011-01-18' ,111)=convert(varchar(10),'2011-01-18 12:00:00',111).

KarinElvira
Starting Member

47 Posts

Posted - 2011-01-17 : 05:07:09

Thanks, I'll try that. My @date still has this format, though: '2011-01-17', and that's the only value I'm able to get from the program so far...
string date = CalendarBokadeMoten.SelectedDate.ToString("yyyy/MM/dd"); //@date
string sql = "SELECT Meetings.ID, Meetings.subject, Meetings.description, Occasion.dateStart, Occasion.dateStop FROM Meetings"
+ " JOIN Occasion ON Meetings.ID = Occasion.meetingID WHERE Meetings.status = 'bokad' AND CONVERT(varchar(10),@date,111) ="
+ " CONVERT(varchar(10),Occasion.dateStart,111)";
Is it possible to JOIN two tables when using 'SELECT *'?

kashyap.2000
Starting Member

22 Posts

Posted - 2011-01-17 : 05:10:24

quote:
Originally posted by KarinElvira

Hmm... that's a bit above my level, I think. I get the date like this:
string date = CalendarBokadeMoten.SelectedDate;
but can't find varchar as an option to convert to, only char.
My sql statement looks like this:
string sql = "SELECT Meetings.ID, Meetings.subject, Meetings.description, Occasion.dateStart, Occasion.dateStop FROM Meetings"
+ " JOIN Occasion ON Meetings.ID = Occasion.meetingID WHERE Meetings.status = 'bokad' AND CONVERT(VARCHAR(50), Occasion.dateStart, 101) = @date";

Can you try the following code?

string sql = "SELECT Meetings.ID, Meetings.subject, Meetings.description, Occasion.dateStart, Occasion.dateStop FROM Meetings"
+ " JOIN Occasion ON Meetings.ID = Occasion.meetingID WHERE Meetings.status = 'bokad' AND CONVERT(VARCHAR(50), Occasion.dateStart, 101) = CONVERT(VARCHAR(50),convert(datetime, @date), 101)";

I love My India.

KarinElvira
Starting Member

47 Posts

Posted - 2011-01-17 : 05:38:34

kashyap.2000: It works! Thank you for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources