Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
I am creating one application in ado.net. In this application I create one Excel file which is sent to the client side. In that Excel file I have patient data. My problem is I want to show only the latest data which is entered between Monday and Saturday. There should not be showing any previous data entered (by? ... about) user. Any tips or an idea would really be appreciated. Below I show you my table structure:
RNo RDate      PName              DOB          CNo   Occupation Sex
1   10/01/2013 Mr. Akshay Dighe   05/10/1987 45444   abc        male 
2   11/01/2013 ms.        kavita  06/04/1987  6565   abc        female
Desired output:
RNo RDate      PName              DOB          CNo   Occupation Sex
1   11/01/2013 ms.        kavita  06/04/1987  6565   abc        female 
2   10/01/2013 Mr. Akshay Dighe   05/10/1987 45444   abc        male
Hope you are all getting my point. What I want to say. Exactly.
 
I wrote one sql query but it is not working. The error message tells me:
'RDate' is not a recognized datepart option
Here is my query:
SELECT p1.PName,p1.RDate,p1.CNo,p1.Sex,p2.ww FROM Physio_cureTable p1
  INNER JOIN(SELECT MAX(RDate)mxdate,DATEPART(RDate,RDate)ww
    FROM Physio_cureTable
     GROUP BY DATEPART(RDate,RDate))p2
       ON p1.RDate=p2.mxdate
        AND datepart(RDate,p1.[RDate])=t2.ww
Posted 27-Jan-13 20:57pm
Edited 28-Jan-13 8:28am
(no name)10.3K
v2
Comments
RedDK at 28-Jan-13 13:33pm
   
I edited your question extensively so that it makes a little more sense. But really, there's a JOIN you're making here and no mention of THAT table (no 1). Secondly, the specific error refers to the function DATEPART(). It takes arguments for which you haven't provided a correct format. I would suggest highlighting each method in your code and hitting F1 so that the HELP volume opens up. The examples there in the BOL make everything very clear.
jibesh at 28-Jan-13 20:03pm
   
what is the data type of RDate field?
Atul Rokade at 29-Jan-13 10:59am
   
jibesh sir it varchar
RedDK at 29-Jan-13 14:09pm
   
<pre>
/* off the "Copy Code" type (whatever that might be) */
 
SELECT DATEPART(year,'10/01/2013')
 
/* See? TSQL is very smart. It knows what the type is ... BUT!*/
 
CREATE TABLE [instance].[cpqa].[tblRA_no_01]( [RNo][int], [RDate][datetime],
 
[PName][nvarchar](78), [DOB][datetime], [CNo][int],
 
[Occupation][nvarchar](22), [Sex][nvarchar](16) )
 
BULK INSERT [instance].[cpqa].[tblRA_no_01] FROM 'C:\Users\RA\copycode.txt'
 
SELECT * FROM [instance].[cpqa].[tblRA_no_01]
 
/* Without specifying format in datetime type you get a default. Now apply that same "smart" DATEPART but leverage some more TSQL "system" by using SELECT */
 
SELECT DATEPART(year,(SELECT [RDate] FROM [instance].[cpqa].[tblRA_no_01] WHERE [RNo] = 2)) AS [RAYear]
 
/* Got the same result ... Now, I could have CAST your [nvarchar] as datetime and everything would still work ... */
 
SELECT DATEPART(year,CAST('10/01/2013'AS datetime)) AS [RAYear]/*
 
But it actually makes more sense to use datetime as the type since there's a variety of
 
formats available ... like I said yesterday, see the BOL for SQL2005. If you haven't got it,
 
download it because having the help file for the app you're using is highly recommended.
 
*/
</pre>

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

  Print Answers RSS
0 OriginalGriff 240
1 Kamal Rocks 184
2 PIEBALDconsult 150
3 BillWoodruff 148
4 Jochen Arndt 135
0 OriginalGriff 5,695
1 DamithSL 4,506
2 Maciej Los 4,007
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,190


Advertise | Privacy | Mobile
Web01 | 2.8.141216.1 | Last Updated 28 Jan 2013
Copyright © CodeProject, 1999-2014
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