Click here to Skip to main content
11,576,891 members (53,714 online)
Rate this: bad
good
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
Edited 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 at 29-Dec-12 3:13am
   
yes your solution is right,i want this query in linq,
and linq not support DATEPART function,
__TR__ at 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 at 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__ at 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 at 2-Jan-13 3:36am
   
Good answer, 5.
__TR__ at 2-Jan-13 5:37am
   
Thank you.
Happy new year :)
Mika Wendelius at 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
0 Sergey Alexandrovich Kryukov 221
1 DamithSL 155
2 Abhinav S 130
3 OriginalGriff 95
4 Lucifier Rocks 85
0 OriginalGriff 715
1 Sergey Alexandrovich Kryukov 631
2 Abhinav S 528
3 F-ES Sitecore 420
4 Suvendu Shekhar Giri 365


Advertise | Privacy | Mobile
Web03 | 2.8.150603.1 | Last Updated 29 Dec 2012
Copyright © CodeProject, 1999-2015
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