DateAdd - 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

General SQL Server Forums

New to SQL Server Administration

DateAdd

Author

Topic

Rach2009
Starting Member

37 Posts

Posted - 2010-02-25 : 06:12:31

Hi All,

I am trying to create a task in the import wizard, and in doing so need to write a query that returns data with yeasterdays date. The query will take the basic form of

SELECT * from table where date=yesterdays date.

I have tried using dateadd, but this is not working properly. Can I use dateadd in this situation? Or is there a simpler way of getting yesterdays day back?

Thanks

Rachel

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-02-25 : 06:14:04

SELECT * from table where date=getdate()-1

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-25 : 06:27:31

quote:
Originally posted by senthil_nagore

SELECT * from table where date=getdate()-1

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Note that getdate() has time too and will not get all rows related to yesterday's day. The correct method is

SELECT * from table
where
date>=dateadd(day,datediff(day,0,getdate()),-1) and
date<dateadd(day,datediff(day,0,getdate()),0)

Madhivanan

Failing to plan is Planning to fail

Rach2009
Starting Member

37 Posts

Posted - 2010-02-25 : 06:41:18

Thanks for this.

I'm afraid I have a further problem though as the table I am importing from stores dates as text, for example '02-FEB-2010' as opposed to '02/02/2010'. How does this affect the statement?

Also, with the dateadd do I need to include additonal " or ' around any of the WHERE clause? I seem to have to include extra " in the rest of the statement that I wouldn't normally need if working within the usual SQL environment (for example, around the table name).

Many Thanks for your input

Rachel

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-25 : 07:21:44

Post the code you are using now

Madhivanan

Failing to plan is Planning to fail

Rach2009
Starting Member

37 Posts

Posted - 2010-02-25 : 09:42:46

select * from "DCSDBA"."SKU_AUDIT" where "audit_dstamp">=dateadd(day,datediff(day,0,getdate()),-1)
and "audit_dstamp"<dateadd(day,datediff(day,0,getdate()),0)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-25 : 09:46:17

quote:
Originally posted by Rach2009

select * from "DCSDBA"."SKU_AUDIT" where "audit_dstamp">=dateadd(day,datediff(day,0,getdate()),-1)
and "audit_dstamp"<dateadd(day,datediff(day,0,getdate()),0)

isnt this working for you?

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

Rach2009
Starting Member

37 Posts

Posted - 2010-02-25 : 09:52:37

No, I get the error message "DATEADD:Invalid Operator". Do you think there should be some additonal " or ' around this part?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-25 : 10:01:02

are you using MS SQL Server?

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

Rach2009
Starting Member

37 Posts

Posted - 2010-02-25 : 10:07:59

Yes - but I am within the import/export wizard. However, the source I am importing from is "Microsoft OLE DB Provider for Oracle" - which I assume is why I need the additional "'s that I wouldn't normally need if just in MS SQL Server?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-25 : 10:17:57

quote:
Originally posted by Rach2009

Yes - but I am within the import/export wizard. However, the source I am importing from is "Microsoft OLE DB Provider for Oracle" - which I assume is why I need the additional "'s that I wouldn't normally need if just in MS SQL Server?

is it export-import wizard in mssql?

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

Rach2009
Starting Member

37 Posts

Posted - 2010-02-25 : 10:18:57

Yes -it's MS SQL Server Import and Export Wizard

Rach2009
Starting Member

37 Posts

Posted - 2010-02-25 : 10:46:37

If I am importing from an Oracle-based database though, will I need to use Oracle language? E.g Sysdate?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-01 : 00:44:08