Click here to Skip to main content
13,256,716 members (51,514 online)
Rate this:
Please Sign up or sign in to vote.
I am creating one application in 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
    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
Updated 28-Jan-13 8:28am
RedDK 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 28-Jan-13 20:03pm
what is the data type of RDate field?
Atul Rokade 29-Jan-13 10:59am
jibesh sir it varchar
RedDK 29-Jan-13 14:09pm
/* 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.


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 |
Web01 | 2.8.171114.1 | Last Updated 28 Jan 2013
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