Click here to Skip to main content
12,956,410 members (69,892 online)
Rate this:
 
Please Sign up or sign in to vote.
See more:
I have a table "DOB",
Which have two columns "Name" and "dateOfBirth",
now i want calculate total rows(result) which have
1. jan month,2012 year,
2. feb month,2012 year,
etc,

so how i use where condition, in the same column name "dateOfBirh" ,
which is in date time formet,
like 12/7/2012 12:24:00:PM,



Plz provide solution In Linq,
because i found solution in Sql like this,


select * from TableName
 
where datepart(mm, DateColumnName) = 1
 
and datepart(yyyy, DateColumnName) = 2012
Posted 28-Dec-12 20:31pm
Updated 28-Dec-12 22:55pm
v5
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

I am assuming that you want to retrieve all the records where date of birth is on Jan 2012 or Feb 2012. If that's the case you can use DATEPART[^] to filter your rows.
try
SELECT COUNT(*) AS TotalRows FROM DOB
WHERE (DATEPART(Month,dateOfBirth) = 1 OR DATEPART(Month,dateOfBirth) = 2) AND (DATEPART(year,dateOfBirth) = 2012)


Edit:
SqlFunctions Class[^] might help you. Take a look at SqlFunctions.DatePart Method[^].
Also check out this CP article LINQ query to compare only date part of DateTime[^]
  Permalink  
v3
Comments
Arun1990 29-Dec-12 3:13am
   
yes your solution is right,i want this query in linq,
and linq not support DATEPART function,
__TR__ 29-Dec-12 4:30am
   
SqlFunctions Class[^] might help you. Take a look at SqlFunctions.DatePart Method[^].
Also check out this CP article LINQ query to compare only date part of DateTime[^]
Arun1990 29-Dec-12 4:53am
   
thanks,your last solution is work fine,
thanks for it, i am trying for this solution at so many days,
but today i found the solution,with the help of you
__TR__ 29-Dec-12 5:00am
   
You are welcome. Glad it worked out for you. I have updated my answer to include those links to MSDN and the CP article so that it will be helpful for anyone referring this thread in the future.
Mika Wendelius 2-Jan-13 3:36am
   
Good answer, 5.
__TR__ 2-Jan-13 5:37am
   
Thank you.
Happy new year :)
Mika Wendelius 2-Jan-13 14:25pm
   
Thanks, Happy New Year to you too :)
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

solution is:
var data1 = context.t_quoted_value.Where(x => x.region_name == "Hong Kong"
                            && x.price_date.Value.Year == dt.Year
                            && x.price_date.Value.Month == dt.Month
                            && x.price_date.Value.Day == dt.Day).ToList();
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

    Print Answers RSS
Top Experts
Last 24hrsThis month
OriginalGriff 6,869
CHill60 3,550
Maciej Los 3,302
ppolymorphe 2,080
Jochen Arndt 1,973


Advertise | Privacy | Mobile
Web02 | 2.8.170525.1 | Last Updated 29 Dec 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100