Help with SQL DateTime

hello, i created a web app. that will query the SQL DB for the DateTime. My SQL DB has the following DateTime format: '2003-08-06 08:55:00.000', but when i i query the database and show the results to my webpage it displays a different format: '8/6/2003 8:55:00 AM', and also when i try to query the database again with the DateTime it gave to me it returns no result. why is this happening? please help... thanks!show me your sql, and what object are you setting the datetime field to?

Help with SQL

I have a table named "policy_details" having four columns called policy_details(varchar),effectdate(datetime),Historyid(int) and policy_status(varchar).

I want to keep only the maximum historyid records in "policy_details" where policy_details and effectdate should be equal and policy_status should be '30240084' . I want to delete the remaining records where policy_details and effectdate should be equal and policy_status should be '30240084'.

Cheers

Praveen

So if you order by policy_details ASC, affectdate ASC, historyid DESC then you only want to keep the first row from each (policy_details, affectdate) group.

I would use the ROW_NUMBER() function and delete where it's not 1

DELETE
FROM policy_details
WHERE ROW_NUMBER() OVER ( PARTITION BY policy_details, affectdate
ORDER BY historyid DESC
) > 1
AND policy_status = '30240084'

|||

Adam,

Thanks for your query.

I am getting an error "The ranking function "ROW_NUMBER" must have an ORDER BY clause."

One more thing is i want same effectdate with only date but not time part.

DELETE
FROM policy_details
WHERE ROW_NUMBER() OVER ( PARTITION BY policy_details,CONVERT(CHAR(10),effectdate,103)
ORDER BY historyid DESC
) > 1
AND policy_status = '30240084'

I did not understand "The ranking function "ROW_NUMBER" must have an ORDER BY clause" error though it is having order by clause. Any ideas?

Cheers

Praveen

|||

Use A CTE, it will work:

WITH myCTE

AS

(SELECT ROW_NUMBER() OVER ( PARTITION BY policy_details,CONVERT(CHAR(10),effectdate,103) ORDER BY historyid DESC) as num

FROM policy_details

WHERE policy_status = '30240084')

DELETE FROM myCTE WHERE num>1

|||

Interesting, when I write the following query, which is almost exactly like yours:

begin transaction

DELETE
FROM person.address
WHERE ROW_NUMBER() OVER ( PARTITION BY addressId
ORDER BY addressLine1 DESC
) > 1
and addressLine1 = 'fred'
rollback transaction

The error I get is:

Msg 4108, Level 15, State 1, Line 3
Windowed functions can only appear in the SELECT or ORDER BY clauses.

(which is more clear).

|||Hello,
After some research, I tested out this syntax which works:
DELETE c
FROM (SELECT * FROM (SELECT RANK() OVER (PARTITION BY policy_details,CONVERT(CHAR(10),effectdate,103) ORDER BY historyid DESC) as num
FROM policy_details WHERE policy_status = '30240084' ) AS t
WHERE t.num>1) c|||

Hi Limno,

Thanks for your help with the SQL which works fine with my data.

I have written a small strored proc which deletes some data based on certain criteria which works fine but it takes approximately 30 minutes for one million records. Is there any way to write this stored proc logic into a single SQl query or sub queries?

The SP is :

-- ***********************************************************************

declare @.policy_details_id uniqueidentifier

declare @.Prepolicy_details_id uniqueidentifier

declare @.EffectDate datetime

declare @.CloseDate datetime

declare @.historyid int

DECLARE DATECur CURSoR FOR

select distinct(policy_details_id),effectdate,closedate,historyid from SM_Cust_policy_Details

where closedate = '2079-06-06 00:00:00.000' and derivative = 0

order by policy_details_id,effectdate

OPEN DATECur

FETCH NEXT FROM DATECur INTO @.policy_details_id, @.effectdate,@.CloseDate,@.historyid

WHILE @.@.FETCH_STATUS = 0

BEGIN

DELETE SM_Cust_policy_details where policy_details_id=@.policy_details_id

and historyid <> @.historyid

and closedate <> '2079-06-06 00:00:00.000'

and CONVERT(DATETIME,CONVERT(CHAR(10),effectdate,103)) >= CONVERT(DATETIME,CONVERT(CHAR(10),@.effectdate,103))

and policy_status_id = 30240084

and derivative = 0

FETCH NEXT FROM DATECur INTO @.policy_details_id, @.effectdate,@.CloseDate,@.historyid

END

CLOSE DATECur

DEALLOCATE DATECur

-- ***********************************************************************

Once again thanks for your help..

Cheers

Praveen

|||Can you explain what the query is trying to do in english rather than trying to decode your criteria. The potential query may differ depending the nature of the data in your table e.g. uniqueness and nullability of the columns.|||

Hi

I need to delete the table data based on the following criteria.

1) "policy_details_id" should be same

2) "effectdate" is greater than or equal to effectdate

3) "HistoryId" should not be same

4) "closedate" is not equal to ' 2079-06-06 00:00:00.000 '

5) "policy_status_id" should be 30240084

6) "Derivative" should be 0

Cheers

Praveen

|||

I can read your code. I got that much from the stored proc you posted. I'm trying to understand what the stored procedure is trying to achieve.

Should just one row per policy_details_id remain after the delete? Why are you doing distinct of the policy_details_id in you cursor query? why delete where effectdate is greater than @.effectdate? Wouldn't that delete more recent rows.

Like I said, I'm trying to understand what this query is doing "in english" and not "in SQL". An explanation similar to your original posting is what I'm after.

|||

Hi Adam,

Thanks for your reply and here are the answers for your queries.

******** Should just one row per policy_details_id remain after the delete?

Yes, I want to keep only one row per policy_details_id after the deletion.

****** Why are you doing distinct of the policy_details_id in you cursor query?

I used distinct caluse in order to reduce the cursor result set in stored proc. Anyway you can avoid distinct caluse in the sql query.

******** why delete where effectdate is greater than @.effectdate? Wouldn't that delete more recent rows

Yes, it is going to delete more recent rows.

Cheers

Praveen

|||

Hello:

Please check this query :

DELETE c

FROM (SELECT * FROM (SELECT *, RANK() OVER (PARTITION BY policy_details_id ORDER BY historyid DESC, effectdate) as num

FROM policy_details WHERE (closedate<> '2079-06-06 00:00:00.000' OR closedate is NULL) AND Derivative=0 AND policy_status_id = 30240084 ) AS t

WHERE t.num>1) c

|||

Hi Limno,

Needs to change the query.

First i need to do the following query..

1) select policy_details_id,effectdate,closedate,historyid from SM_Cust_policy_Details where closedate = '2079-06-06 00:00:00.000' and derivative = 0

Based on the result set of this query i need to do the following delete query.

2) DELETE SM_Cust_policy_details where policy_details_id = resultset_policy_details_id and historyid <> resultset_historyid and closedate <> '2079-06-06 00:00:00.000' and CONVERT(DATETIME,CONVERT(CHAR(10),effectdate,103))>=CONVERT(DATETIME,CONVERT(CHAR(10),resultset_effectdate,103)) and policy_status_id=30240084 and derivative = 0.

Note: resultset_policy_details_id,resultset_historyid,resultset_effectdate are the result set query values of policy_details_id,historyid,effectdate in query number 1.

How can i integrate both of the above queries and make a single query?

Cheers

Praveen

|||

Could you post a set of your sample data in your table and the expected result? Thanks.

|||

The data consists like this..

policy_details_id policy_status_id historyid effectdate closedate

70C36E97-9564-A048-0000-9665018B81FF 30240084 9 2004-09-11 12:00:00.000 2004-10-11 11:34:00.000
70C36E97-9564-A048-0000-9665018B81FF 30240084 10 2004-10-11 11:34:00.000 2005-09-11 11:47:00.000
70C36E97-9564-A048-0000-9665018B81FF 30240084 11 2005-09-11 12:00:00.000 2005-09-11 12:00:00.000
70C36E97-9564-A048-0000-9665018B81FF 14075352 12 2005-09-11 11:47:00.000 2079-06-06 00:00:00.000

First i need to consider the effectdate where closedate is '2079-06-06 00:00:00.000'.In this case it is '2005-09-11 11:47:00.000'.

I need to delete the records where effectdate(only datepart) is equal or greater than '2005-09-11' and policy_status_id should be 30240084.

In the above case the third record i.e historyid = 11 is going to be deleted.

Let me know if you have any problems to understand.

Cheers

Praveen

Help with reading datetime with DATEPART

I was hoping someone could help me with the sql syntax in trying to return the date from a datetime value. I'm trying to get the month and day and year from a datetime value in the database but I keep getting a token error. This is the code I'm using to try to read the date, from everything I've read for sql, it should work but it doesn't.

Dim sql As String = "SELECT * FROM People WHERE DATEPART(month, dtime) = '" & _
DateTime.Month & "' & DATEPART(year, dtime) = '" & DateTime.Year & '"

Dim Sqlreader As SqlCeDataReader = cmd.ExecuteReader

The error I get is:
There was an error parsing the query. [ Token line number = 1,Token line offset = 82,Token in error = = ]

It doesn't seem to recognize the second DATEPART search and the = sign is a syntax error.

What am I doing wrong here?

in the "& DATEPART(year, dtime)" part, replace "&" with "and"

regards

|||Hmmm, I tried the '&' symbol and also tried 'AND' but not a lowercase 'and'.
Thanks.

crt

Help with large text fields please

I have the following problem (MS SQL 2000):
A modest table with several large fields (currently varchar(5000)), plus some datetime and integer fields recording who's done what and when.

Two problems - (1) I now realise that I'm limited to 8060 characters, and (2) users seem to think even 5000 chars might be too small on occasions (the table is for recording laboratory problems, so the amount of text depends on what the local quality manager finds!)

I thought I'd change my varchars to text. However, when I changed just one of them to text, the record set being returned by my stored procedure has lots of empty fields. The query ran OK before the datatype change and STILL runs OK in Enterprise Manager after the change.

The basic query is:

Code: ( text )

1. SELECT TechAnomalies.*,

2. lab1.LabRef AS labref1, lab1.LabName AS labname1, lab1.EULabRef AS EULabRef,

3. lab2.LabRef AS labref2, lab2.LabName AS labname2,

4. u1.UserFullName AS RaisedBy,

5. u2.UserFullName AS Inter,

6. u3.UserFullName AS SignOffBy,

7. u4.UserFullName AS LastEditor,

8. u5.UserFullName AS LQM

9. FROM dbo.TechAnomalies

10. INNER JOIN dbo.Labs AS lab1

11. ON lab1.LabID = TechAnomalies.TALabID

12. LEFT OUTER JOIN dbo.Labs AS lab2

13. ON lab2.LabID = TechAnomalies.TAIntermedLabID

14. LEFT OUTER JOIN dbo.Users AS u1

15. ON u1.UserID = TechAnomalies.TARaiserUserID

16. LEFT OUTER JOIN dbo.Users AS u2

17. ON u2.UserID = TechAnomalies.TAIntermedUserID

18. LEFT OUTER JOIN dbo.Users AS u3

19. ON u3.UserID = TechAnomalies.TASignedOffBy

20. LEFT OUTER JOIN dbo.Users AS u4

21. ON u4.UserID = TechAnomalies.TALastEditedBy

22. LEFT OUTER JOIN dbo.Users AS u5

23. ON u5.UserID = TechAnomalies.TALQMReviewBy

I use it with or without a WHERE clause (passed to the stored procedure as a varchar) to return either a recordset or the details of one record.

Any suggestions please?

You could try using nvarchar

Help with Join

H
I have two tables
Create table company
( firm char(32),
p_name char(40),
proj_code char(8),
proj_start_dt datetime
)
go
insert into company values ("ABC","John Doe", "DJOE","Nov 12 2001")
insert into company values ("ABC","Jane Doe", "JANEDOE","Oct 26 2000")
insert into company values ("ABC","Bruce Smith", "BRUCES","Mar 01 2002")
insert into company values ("ABC","David Smith", "SDAVID","Nov 12 2003")
insert into company values ("ABC","Lisa Cox", "LCOX","Apr 15 2004")
go
Create table employee
( firm char(32),
p_name char(40),
proj_end_dt datetime
)
go
insert into employee values ("ABC","John Doe", "Nov 20 2003")
insert into employee values ("ABC","Jane Doe", "Dec 26 2002")
insert into employee values ("ABC","Bruce Smith","Apr 01 2003")
go
I need to extract data for p_name with proj_start_dt > proj_end_dt
my query is not working
select c.firm,
c.p_name,
c.proj_code,
c.proj_start_dt
from company c
left join employee e
on c.p_name=e.p_name
and c.proj_start_dt > e.proj_end_dt
go
returns all values
firm p_name
proj_code proj_start_dt
-- --- --
-- --
ABC John Doe
DJOE Nov 12 2001 12:00AM
ABC Jane Doe
JANEDOE Oct 26 2000 12:00AM
ABC Bruce Smith
BRUCES Mar 1 2002 12:00AM
ABC David Smith
SDAVID Nov 12 2003 12:00AM
ABC Lisa Cox
LCOX Apr 15 2004 12:00AM
select c.firm,
c.p_name,
c.proj_code,
c.proj_start_dt
from company c,
employee e
where c.p_name=e.p_name
and c.proj_start_dt > e.proj_end_dt
go
returns no values
firm p_name
proj_code proj_start_dt
-- --- --
-- --
Could someone please help.
AjmisterThanks for posting the DDL and sample data. Could you show and explain
exactly what result you want. As far as I can see there are no rows where
Proj_start_dt is greater than Proj_end_dt for any given P_name.
David Portas
SQL Server MVP
--|||try this...
select c.firm,
c.p_name,
c.proj_code,
c.proj_start_dt
from company c
left join employee e
on c.p_name=e.p_name
where c.proj_start_dt > e.proj_end_dt
"ajmister" wrote:

> H
> I have two tables
> Create table company
> ( firm char(32),
> p_name char(40),
> proj_code char(8),
> proj_start_dt datetime
> )
> go
> insert into company values ("ABC","John Doe", "DJOE","Nov 12 2001")
> insert into company values ("ABC","Jane Doe", "JANEDOE","Oct 26 2000")
> insert into company values ("ABC","Bruce Smith", "BRUCES","Mar 01 2002")
> insert into company values ("ABC","David Smith", "SDAVID","Nov 12 2003")
> insert into company values ("ABC","Lisa Cox", "LCOX","Apr 15 2004")
> go
>
> Create table employee
> ( firm char(32),
> p_name char(40),
> proj_end_dt datetime
> )
> go
> insert into employee values ("ABC","John Doe", "Nov 20 2003")
> insert into employee values ("ABC","Jane Doe", "Dec 26 2002")
> insert into employee values ("ABC","Bruce Smith","Apr 01 2003")
> go
> I need to extract data for p_name with proj_start_dt > proj_end_dt
> my query is not working
>
> select c.firm,
> c.p_name,
> c.proj_code,
> c.proj_start_dt
> from company c
> left join employee e
> on c.p_name=e.p_name
> and c.proj_start_dt > e.proj_end_dt
> go
> returns all values
> firm p_name
> proj_code proj_start_dt
> -- ---
--
> -- --
> ABC John Doe
> DJOE Nov 12 2001 12:00AM
> ABC Jane Doe
> JANEDOE Oct 26 2000 12:00AM
> ABC Bruce Smith
> BRUCES Mar 1 2002 12:00AM
> ABC David Smith
> SDAVID Nov 12 2003 12:00AM
> ABC Lisa Cox
> LCOX Apr 15 2004 12:00AM
>
> select c.firm,
> c.p_name,
> c.proj_code,
> c.proj_start_dt
> from company c,
> employee e
> where c.p_name=e.p_name
> and c.proj_start_dt > e.proj_end_dt
> go
> returns no values
> firm p_name
> proj_code proj_start_dt
> -- ---
--
> -- --
> Could someone please help.
>
> Ajmister
>
>|||Did you try it? :-)
David Portas
SQL Server MVP
--|||Sorry abt my previous post, that was wrong.
here is the answer,
the company table has the proj_start_dt = Apr 15 2004 which is greater than
the proj_end date.But the value of p_name in the company table is Lisa Cox
which is not there in the employee table.So when you do a join you will not
get the result.
"ajmister" wrote:

> H
> I have two tables
> Create table company
> ( firm char(32),
> p_name char(40),
> proj_code char(8),
> proj_start_dt datetime
> )
> go
> insert into company values ("ABC","John Doe", "DJOE","Nov 12 2001")
> insert into company values ("ABC","Jane Doe", "JANEDOE","Oct 26 2000")
> insert into company values ("ABC","Bruce Smith", "BRUCES","Mar 01 2002")
> insert into company values ("ABC","David Smith", "SDAVID","Nov 12 2003")
> insert into company values ("ABC","Lisa Cox", "LCOX","Apr 15 2004")
> go
>
> Create table employee
> ( firm char(32),
> p_name char(40),
> proj_end_dt datetime
> )
> go
> insert into employee values ("ABC","John Doe", "Nov 20 2003")
> insert into employee values ("ABC","Jane Doe", "Dec 26 2002")
> insert into employee values ("ABC","Bruce Smith","Apr 01 2003")
> go
> I need to extract data for p_name with proj_start_dt > proj_end_dt
> my query is not working
>
> select c.firm,
> c.p_name,
> c.proj_code,
> c.proj_start_dt
> from company c
> left join employee e
> on c.p_name=e.p_name
> and c.proj_start_dt > e.proj_end_dt
> go
> returns all values
> firm p_name
> proj_code proj_start_dt
> -- ---
--
> -- --
> ABC John Doe
> DJOE Nov 12 2001 12:00AM
> ABC Jane Doe
> JANEDOE Oct 26 2000 12:00AM
> ABC Bruce Smith
> BRUCES Mar 1 2002 12:00AM
> ABC David Smith
> SDAVID Nov 12 2003 12:00AM
> ABC Lisa Cox
> LCOX Apr 15 2004 12:00AM
>
> select c.firm,
> c.p_name,
> c.proj_code,
> c.proj_start_dt
> from company c,
> employee e
> where c.p_name=e.p_name
> and c.proj_start_dt > e.proj_end_dt
> go
> returns no values
> firm p_name
> proj_code proj_start_dt
> -- ---
--
> -- --
> Could someone please help.
>
> Ajmister
>
>|||Your first join is returning all rows because you are doing an outer join
which is telling SQL you want to return all row from the set on the left
(company) even if they don't match any rows in the set on the right
(employee). If you run the following command you can see you are not
returning any values from the employee table:
select c.*, e.*
from company c
left join employee e
on c.p_name=e.p_name
and c.proj_start_dt > e.proj_end_dt
go
Your second query seems to be doing what you want, and you are not getting
any rows returned because the are no company.proj_start_dt's that are > the
employee.proj_end_dt when the company.p_name and employee.p_name are equal.
Hope this helps explain why you are getting the results you posted.
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"ajmister" <ajmister@.optonline.net> wrote in message
news:etG3OUXCFHA.4052@.TK2MSFTNGP15.phx.gbl...
> H
> I have two tables
> Create table company
> ( firm char(32),
> p_name char(40),
> proj_code char(8),
> proj_start_dt datetime
> )
> go
> insert into company values ("ABC","John Doe", "DJOE","Nov 12 2001")
> insert into company values ("ABC","Jane Doe", "JANEDOE","Oct 26 2000")
> insert into company values ("ABC","Bruce Smith", "BRUCES","Mar 01 2002")
> insert into company values ("ABC","David Smith", "SDAVID","Nov 12 2003")
> insert into company values ("ABC","Lisa Cox", "LCOX","Apr 15 2004")
> go
>
> Create table employee
> ( firm char(32),
> p_name char(40),
> proj_end_dt datetime
> )
> go
> insert into employee values ("ABC","John Doe", "Nov 20 2003")
> insert into employee values ("ABC","Jane Doe", "Dec 26 2002")
> insert into employee values ("ABC","Bruce Smith","Apr 01 2003")
> go
> I need to extract data for p_name with proj_start_dt > proj_end_dt
> my query is not working
>
> select c.firm,
> c.p_name,
> c.proj_code,
> c.proj_start_dt
> from company c
> left join employee e
> on c.p_name=e.p_name
> and c.proj_start_dt > e.proj_end_dt
> go
> returns all values
> firm p_name
> proj_code proj_start_dt
> -- ---
--
> -- --
> ABC John Doe
> DJOE Nov 12 2001 12:00AM
> ABC Jane Doe
> JANEDOE Oct 26 2000 12:00AM
> ABC Bruce Smith
> BRUCES Mar 1 2002 12:00AM
> ABC David Smith
> SDAVID Nov 12 2003 12:00AM
> ABC Lisa Cox
> LCOX Apr 15 2004 12:00AM
>
> select c.firm,
> c.p_name,
> c.proj_code,
> c.proj_start_dt
> from company c,
> employee e
> where c.p_name=e.p_name
> and c.proj_start_dt > e.proj_end_dt
> go
> returns no values
> firm p_name
> proj_code proj_start_dt
> -- ---
--
> -- --
> Could someone please help.
>
> Ajmister
>|||Thank you for a quick response, sorry I want to extract all Proj_start_dt
that do not have a proj_end_dt
Thanx
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:taednVcaFv9azpzfRVn-sg@.giganews.com...
> Thanks for posting the DDL and sample data. Could you show and explain
> exactly what result you want. As far as I can see there are no rows where
> Proj_start_dt is greater than Proj_end_dt for any given P_name.
> --
> David Portas
> SQL Server MVP
> --
>|||Yes, but it did not return any values
firm p_name
proj_code proj_start_dt
-- --- --
-- --
I would the query to return proj_start_dt whihc do not have proj_end_dt
(sorry about the previous error)
example
firm p_name
proj_code proj_start_dt
-- --- --
-- --
ABC David Smith
SDAVID Nov 12 2003 12:00AM
ABC Lisa Cox
LCOX Apr 15 2004 12:00AM
Thank you
Ajmister
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:yt6dnXskg5uRy5zfRVn-vg@.giganews.com...
> Did you try it? :-)
> --
> David Portas
> SQL Server MVP
> --
>|||Try this:
SELECT C.firm, C.p_name, C.proj_code, C.proj_start_dt
FROM company AS C
LEFT JOIN employee AS E
ON C.p_name = E.p_name
AND C.proj_start_dt < E.proj_end_dt
WHERE C.proj_start_dt IS NOT NULL
AND E.proj_end_dt IS NULL
David Portas
SQL Server MVP
--|||Thank you sir. That gave me the correct output.
Ajmister
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:OIydnb5UiOmFGZzfRVn-iA@.giganews.com...
> Try this:
> SELECT C.firm, C.p_name, C.proj_code, C.proj_start_dt
> FROM company AS C
> LEFT JOIN employee AS E
> ON C.p_name = E.p_name
> AND C.proj_start_dt < E.proj_end_dt
> WHERE C.proj_start_dt IS NOT NULL
> AND E.proj_end_dt IS NULL
> --
> David Portas
> SQL Server MVP
> --
>