unable to capture few records because of timestamp

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

unable to capture few records because of timestamp

Author

Topic

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-11-18 : 18:56:41

Hi
In a query to retreive all records assigned to a person in that particular month i see that my query is not returning few records because it has a different time stamp

My Query

SELECT SR.roc_id, convert(varchar(20),SR.prodmth,101) as prodmth, RD.rptdesctext,C.first_name + space(1) + C.Last_name as AnalystName,SR.opa_id FROM RENEWALS SR INNER JOIN REPORT RD ON SR.rptdesc = RD.rptdesc Inner join Contact C on SR.opa_id = C.contact_id
 WHERE (SR.opa_id = 1 and YEAR(SR.prodmth) = YEAR(getdate()) AND MONTH(SR.prodmth) = MONTH(getdate()))

Query not returning the below record though it is for the current month

148857 2012-11-20 00:00:00.000

Query returning the below records

148858 2012-11-20 12:00:00.000
148859 2012-11-20 12:00:00.000
148860 2012-11-20 12:00:00.000
148861 2012-11-20 12:00:00.000
148862 2012-11-20 12:00:00.000

How do i modify my query so that it does not take the timestamp into consideration.All it should take is the month and year

Thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-18 : 19:15:13

Have you tried with DATEPART Function?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-19 : 01:29:38

just use condition like


...

SR.prodmth>=DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)
AND SR.prodmth < DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)
..

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-11-19 : 08:55:57

I have not used Datepart function.How do i use it here

quote:
Originally posted by sodeep

Have you tried with DATEPART Function?

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-11-19 : 09:00:32

Hi Visakh

i changed my query like below and still does not get all records


SELECT SR.roc_id, convert(varchar(20),SR.prodmth,101) as prodmth, RD.rptdesctext,C.first_name + space(1) + C.Last_name as AnalystName,SR.opa_id FROM RENEWALS SR INNER JOIN REPORT RD ON SR.rptdesc = RD.rptdesc Inner join Contact C on SR.opa_id = C.contact_id

 WHERE (SR.opa_id = 1 and  SR.prodmth>=DATEADD(mm,DATEDIFF(year,0,GETDATE()),0)
AND SR.prodmth < DATEADD(year,DATEDIFF(year,0,GETDATE())+1,0) AND  SR.prodmth>=DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)
AND SR.prodmth < DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))

Thanks

quote:
Originally posted by visakh16

just use condition like

...

SR.prodmth>=DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)
AND SR.prodmth < DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)
..

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-19 : 09:09:56

Jim, your original query and the query Visakh posted should have worked and returned the row where prodmth = '2012-11-20 00:00:00.000'. If it is not returning that, possible reasons are:

a) that row is eliminated due to other filters in the where clause or join conditions - for example, check if that row has an opa_id other than 1, or whether rptdesc is null for that row.

b) if the data type of that column is not date time, it can produce unexpected results. You can check the data type using the following query:

SELECT * FROM INFORMATION_SCHEMA.TABLES 
WHERE COLUMN_NAME = 'prodmth' AND TABLE_NAME = 'RENEWALS';

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-11-19 : 09:27:05

Thank You for directing me in the right direction.I see that rptdesc is NULL and this happening after an update query

quote:
Originally posted by sunitabeck

Jim, your original query and the query Visakh posted should have worked and returned the row where prodmth = '2012-11-20 00:00:00.000'. If it is not returning that, possible reasons are:

a) that row is eliminated due to other filters in the where clause or join conditions - for example, check if that row has an opa_id other than 1, or whether rptdesc is null for that row.

b) if the data type of that column is not date time, it can produce unexpected results. You can check the data type using the following query:
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE COLUMN_NAME = 'prodmth' AND TABLE_NAME = 'RENEWALS';

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-19 : 09:34:54

You are quite welcome :)

Couple of comments, once you work out the reasons for the null join:
1. The query Visakh posted can be more efficient than the one you were using. The reason is that his query avoids the per-row function evaluation.

2. Usually people recommend that you don't join on columns such as description (which can be less than precise). I am only guessing from the column name ("rptdesc") that it is some sort of description. If you have a description key or something of that nature, it would be better to join on such key.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-20 : 01:44:53

quote:
Originally posted by jim_jim

Hi Visakh

i changed my query like below and still does not get all records

SELECT SR.roc_id, convert(varchar(20),SR.prodmth,101) as prodmth, RD.rptdesctext,C.first_name + space(1) + C.Last_name as AnalystName,SR.opa_id FROM RENEWALS SR INNER JOIN REPORT RD ON SR.rptdesc = RD.rptdesc Inner join Contact C on SR.opa_id = C.contact_id

WHERE (SR.opa_id = 1 and SR.prodmth>=DATEADD(mm,DATEDIFF(year,0,GETDATE()),0)
AND SR.prodmth < DATEADD(year,DATEDIFF(year,0,GETDATE())+1,0) AND SR.prodmth>=DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)
AND SR.prodmth < DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))

Thanks

quote:
Originally posted by visakh16

just use condition like

...

SR.prodmth>=DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)
AND SR.prodmth < DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)
..

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

the year check is redundant here as you're already having a more restrictive filter based on month

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources