Solved

select birth dates by date and month only

Posted on 2006-11-03
12
319 Views
Last Modified: 2010-04-16
hi there , in my Emplyess table i have birth date to all my workers
i need to query that returns the workers that have a birthDate Beetween day and month

if i have a employee that burn in 20/01/1970

in my Form i want to give Show All burns from 01/01 to 31/01

like that ....

how can i do it ?

thanks ....
0
Comment
Question by:Tech_Men
[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
  • Learn & ask questions
  • 8
  • 4
12 Comments
 
LVL 13

Expert Comment

by:joechina
ID: 17867289
You can compare by string

In SQL

select *
from yourtable
where substring(Convert(varchar(10),birthdaycolumn,101),1,5) between '01/01' and '01/31'

or in C#

birthday.ToString("MMdd") >= "0101" and ...<="0131"
0
 

Author Comment

by:Tech_Men
ID: 17867778
i have tryd to do this :

WHERE     (StudCards.TalmidStat = @Tstat) AND (SUBSTRING(CONVERT(varchar(10), Students.TalmidBirthDate, 101), 1, 5) BETWEEN @b1 AND @b2)

but its dosent work good
0
 

Author Comment

by:Tech_Men
ID: 17867975
wait i think its o.k
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

Author Comment

by:Tech_Men
ID: 17868080
what do i need to send from the dateTimePicker?
0
 

Author Comment

by:Tech_Men
ID: 17868087
i am tryd to send shortdate but its dosent work
0
 

Author Comment

by:Tech_Men
ID: 17868177
i am geeting this exs :

System.Data.SqlClient.SqlException: Syntax error converting datetime from character string.
   at System.Data.SqlClient.SqlDataReader.Read()
0
 

Author Comment

by:Tech_Men
ID: 17868473
i know what is the problem

the datetimepicker give me the date in this format dd/mm/yyyy
the command  line :

  (StudCards.TalmidStat = @Tstat) AND (SUBSTRING(CONVERT(varchar(10), Students.TalmidBirthDate, 101), 1, 5) BETWEEN @b1 AND @b2)

get this format : mm/dd/yyyy

so its need to coorect the command or maybe to pull out another format from the datetimepicker

how can i do it ?
thanks...
0
 
LVL 13

Expert Comment

by:joechina
ID: 17869038
can you just run a sql and post your result?

select CONVERT(varchar(10), getDate(), 101) ?
0
 
LVL 13

Expert Comment

by:joechina
ID: 17869066
ok, I think I know your problem

Would you please post your C# code?

When you pass b1 and b2 into the database,pass the following in

b1 = yourstartdate.ToString("MM/dd");
b2 = yourstartdate.ToString("MM/dd");
0
 

Author Comment

by:Tech_Men
ID: 17870253
its not working this is the button code :
AdByRakaz.SelectCommand.Parameters.Clear();
                              AdByRakaz.SelectCommand.Parameters.Add("@Tstat",SqlDbType.VarChar);
                              AdByRakaz.SelectCommand.Parameters.Add("@b1",SqlDbType.VarChar,10);
                              AdByRakaz.SelectCommand.Parameters.Add("@b2",SqlDbType.VarChar,10);
                        
                              AdByRakaz.SelectCommand.Parameters["@Tstat"].Value=comboBox1.Text;

                              AdByRakaz.SelectCommand.Parameters["@b1"].Value=dateTimePicker1.Value.ToShortDateString("MM:dd");
                              AdByRakaz.SelectCommand.Parameters["@b2"].Value=dateTimePicker2.Value.ToString("MM:DD");

                              AdByRakaz.SelectCommand.CommandText=CmdBtoB.CommandText;

and this is the sql Command :

SELECT Students.TalmidRunId, Students.TalmidID, Students.TalmidFamName, Students.TalmidPriName, Students.TalmidSex, Students.TalmidFatherName, Students.TalmidMotherName, Students.TalmidBirthDate, Students.TalmidBirthCountry, Students.TalmidKopa, Students.TalmidAliaDate, Students.TalmidTeetInsu, Students.TalmidCity, Students.TalmidAddres, Students.TalmidHouseNum, Students.TalimdMikod, Students.TalimdPhone, Students.TalmidCell, Students.TalmidfatherCell, Students.TalmidMotherCell, Students.NaydNosaf, Students.TalmidRemarks, StudCards.CardsRunId, StudCards.CardStudId, StudCards.KlitaDate, StudCards.LeaveDate, StudCards.Class, StudCards.Makbila, StudCards.Megma, StudCards.GoremMemamen, StudCards.SpicealEdication, StudCards.TalmidStat, StudCards.RakazNum, StudCards.EosNum, StudCards.GuidNum, StudCards.HouseFatherNum, StudCards.StudRemarks, StudCards.Zav1, StudCards.Zav2, StudCards.Zav3, StudCards.Zav1Date, StudCards.Zav2date, StudCards.Zav3date, StudCards.MegamaCode, GoremMafne.GoremMafneRunId, GoremMafne.GMStudID, GoremMafne.GoremName, GoremMafne.GoremTafkid, GoremMafne.GoremPhone, GoremMafne.GoremPhone2, GoremMafne.GoremFax, GoremMafne.GoremCell, GoremMafne.GoremUnitName, GoremMafne.GoremAddres, GoremMafne.GoremCity, GoremMafne.Goremmikod, GoremMafne.GoremRemarks FROM Students INNER JOIN StudCards ON Students.TalmidID = StudCards.CardStudId INNER JOIN GoremMafne ON StudCards.CardStudId = GoremMafne.GMStudID WHERE (StudCards.TalmidStat = @Tstat) AND (SUBSTRING(CONVERT(varchar(10), Students.TalmidBirthDate, 101), 1, 5) BETWEEN @b1 AND @b2)

0
 
LVL 13

Accepted Solution

by:
joechina earned 500 total points
ID: 17871233
Try change
AdByRakaz.SelectCommand.Parameters["@b1"].Value=dateTimePicker1.Value.ToShortDateString("MM:dd");
AdByRakaz.SelectCommand.Parameters["@b2"].Value=dateTimePicker2.Value.ToString("MM:DD");
To
AdByRakaz.SelectCommand.Parameters["@b1"].Value=dateTimePicker1.Value.ToString("MM/dd");
AdByRakaz.SelectCommand.Parameters["@b2"].Value=dateTimePicker2.Value.ToString("MM/dd");
0
 

Author Comment

by:Tech_Men
ID: 17871252
thanks !!!
0

Featured Post

Industry Leaders: 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

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

690 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