Click here to Skip to main content
12,507,674 members (57,422 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL LINQ
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


Advertise | Privacy | Mobile
Web02 | 2.8.160927.1 | Last Updated 29 Dec 2012
Copyright © CodeProject, 1999-2016
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