Solved

select birth dates by date and month only

Posted on 2006-11-03
12
315 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
  • 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction Although it is an old technology, serial ports are still being used by many hardware manufacturers. If you develop applications in C#, Microsoft .NET framework has SerialPort class to communicate with the serial ports.  I needed to…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

895 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now