Solved

select birth dates by date and month only

Posted on 2006-11-03
12
316 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

803 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