SQL Syatem date - 1 - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Syatem date - 1

Author

Topic

cliveW
Starting Member

4 Posts

Posted - 2006-10-12 : 10:01:51

Im currently using this Statement to retrieve previous days business.

and LD.Load_DT >'2006-10-11' and LD.Load_DT < '2006-10-12'

Load_DT is the date the Policy was loaded to database

I want to Use where load_dt = system date(todays) - 1 day

Something like

and LD.Load_DT = format(dateadd(day, -1, now()),'YYYY-MM-DD')

But this does not work

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-12 : 10:06:59

select * from mytable
where load_dt >= dateadd(day, datediff(day, 0, getdate()), -1) and load_dt < dateadd(day, datediff(day, 0, getdate()), 0)

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-12 : 10:08:30

Now() is not SQL server function, use getdate()

and LD.Load_DT = dateadd(day, -1, getdate())

Also why you need to format it?

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-12 : 10:12:00

It not understanding -1, use

select * from mytable
where load_dt >= dateadd(day, datediff(day, 1, getdate()), 0) and load_dt < dateadd(day, datediff(day, 0, getdate()), 0)

Peter Larsson
Helsingborg, Sweden

cliveW
Starting Member

4 Posts

Posted - 2006-10-12 : 10:24:30

Peter using this !
and load_dt >= dateadd(day, datediff(day, 1, getdate()), 0)
and load_dt < dateadd(day, datediff(day, 0, getdate()), 0)

Returns this error !!
<eb1>Function DATEDIFF invoked with wrong number or type of argument(s).
State:HY000,Native:11021,Origin:[DataDirect][ODBC Sybase Wire Protocol driver][SQL Server]</eb1>

cliveW
Starting Member

4 Posts

Posted - 2006-10-12 : 10:26:14

Harsh using your suggestion does not create an error but returns no rows ..

and LD.Load_DT = dateadd(day, -1, getdate())

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-12 : 10:36:29

Which DBMS are you using? For which interpreter is the query parsed?

Peter Larsson
Helsingborg, Sweden

cliveW
Starting Member

4 Posts

Posted - 2006-10-12 : 10:43:39

This is the Full Query. Last lines is what I am having trouble with.(The Date)

I am using a Query Tool using (ODBC)4.1.1.1 (MBCS Build) Sybase ASE ODBC Driver

SELECT P.POLICY_NUMBER,I.Pri_Sec_Ind,P.ACORD_POLSTAT,P.ACORD_STATREASON,O.STAT_DESC,R.REQ_DESCRIPTION,R.Acord_REQSTAT,E.REQSTAT_DESC,C.Contract_Number, N.data_source_system,Ld.Load_dt
FROM
NBSpec as P,
ComInter as C,
Policy as N,
OPEN_ACCESS..POL_STAT_DESC_LU AS O,
OPEN_ACCESS..OLI_LU_REQSTAT AS E,
Pol_ld as Ld,
REQUIREMENTS as R,
Insured as I
where P.POLICY_NUMBER = C.POLICY_NUMBER
and P.POLICY_NUMBER = N.POLICY_NUMBER
and P.POLICY_NUMBER = I.POLICY_NUMBER
and P.POLICY_NUMBER = R.POLICY_NUMBER
and P.Policy_Number = LD.Policy_Number
and R.Acord_REQSTAT = E.REQSTAT_CD
and (P.ACORD_STATREASON <> 1002900172
or P.ACORD_STATREASON is NULL)
and P.ACORD_POLSTAT *= O.POLSTAT_CD
and ((P.ACORD_STATREASON *= O.STATREASON_CD) or
O.STATREASON_CD is null and P.ACORD_STATREASON is null ))
and N.data_source_system = 'PLUS'
and I.Pri_Sec_Ind = 'P'
and P.ACORD_POLSTAT NOT IN (7,39)
and R.Acord_REQSTAT in (2,4,7,10)
and Ld.load_dt >= dateadd(day, datediff(day, 1, getdate()), 0)
and Ld.load_dt < dateadd(day, datediff(day, 0, getdate()), 0)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-12 : 10:50:31

This would be the ANSI-92 SQL syntax to write your query.

SELECT		P.POLICY_NUMBER,
		I.Pri_Sec_Ind,
		P.ACORD_POLSTAT,
		P.ACORD_STATREASON,
		O.STAT_DESC,
		R.REQ_DESCRIPTION,
		R.Acord_REQSTAT,
		E.REQSTAT_DESC,
		C.Contract_Number,
		N.data_source_system,
		Ld.Load_dt
FROM		NBSpec as P, 
INNER JOIN	ComInter as C ON P.POLICY_NUMBER = C.POLICY_NUMBER
INNER JOIN	Policy as N ON P.POLICY_NUMBER = N.POLICY_NUMBER AND N.data_source_system = 'PLUS'
LEFT JOIN	OPEN_ACCESS..POL_STAT_DESC_LU AS O ON P.ACORD_POLSTAT = O.POLSTAT_CD AND P.ACORD_STATREASON = O.STATREASON_CD
INNER JOIN	Pol_ld as Ld ON P.Policy_Number = LD.Policy_Number AND and Ld.load_dt >= dateadd(day, datediff(day, 1, getdate()), 0) and Ld.load_dt < dateadd(day, datediff(day, 0, getdate()), 0)
INNER JOIN	REQUIREMENTS as R ON P.POLICY_NUMBER = R.POLICY_NUMBER AND R.Acord_REQSTAT in (2,4,7,10)
INNER JOIN	OPEN_ACCESS..OLI_LU_REQSTAT AS E ON R.Acord_REQSTAT = E.REQSTAT_CD
INNER JOIN	Insured as I ON P.POLICY_NUMBER = I.POLICY_NUMBER AND I.Pri_Sec_Ind = 'P'
where		(P.ACORD_STATREASON <> 1002900172 or P.ACORD_STATREASON is NULL)
		AND P.ACORD_POLSTAT NOT IN (7,39)

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-12 : 21:12:37

http://www.sql-server-performance.com/fk_datetime.asp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources