Solved

select birth dates by date and month only

Posted on 2006-11-03
12
314 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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Extention Methods in C# 3.0 by Ivo Stoykov C# 3.0 offers extension methods. They allow extending existing classes without changing the class's source code or relying on inheritance. These are static methods invoked as instance method. This…
In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

744 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

15 Experts available now in Live!

Get 1:1 Help Now