Unexpected Result Using DATEADD

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 2012 Forums

Transact-SQL (2012)

Unexpected Result Using DATEADD

Author

Topic

2fire
Starting Member

9 Posts

Posted - 2014-01-16 : 17:26:36

Using DATEADD, I am attempting to select records 1 month prior to the current system date, but results have left me scratching my head. Wondering if anyone has advice as how to remedy this problem.

The following statement -

select CONVERT (VARCHAR(10),date_started,101) AS 'Date Hired', convert(varchar(10),dateadd(m,-1,GetDate()),101) AS 'GetDateTest'
from tablename

returns -
Date Hired GetDateTest
2013-12-16 2013-12-16
2013-12-16 2013-12-16
2013-11-19 2013-12-16
2013-11-07 2013-12-16
2013-11-01 2013-12-16

after adding a WHERE clause -

select CONVERT (VARCHAR(10),date_started,101) AS 'Date Hired', convert(varchar(10),dateadd(m,-1,GetDate()),101) AS 'GetDateTest'
from tablename
where 'date hired' = 'getdatetest'

No records are returned. I would expect at least two.

I get the same results with -

select date_started as 'Date Hired', DATEADD(m,-1,dateadd(dd,0,datediff(dd,0,GetDate()))) AS 'GetDateTest'
from tablename
where 'date hired' = 'getdatetest'

..and

select dateadd(d,0,date_started), DATEADD(m,-1,dateadd(dd,0,datediff(dd,0,GetDate()))) AS 'GetDateTest'
from tablename
where 'date hired' = 'getdatetest'

date_started is a datetime field and defaults to -
2013-12-16 00:00:00.000
2013-12-16 00:00:00.000
2013-11-19 00:00:00.000
2013-11-07 00:00:00.000

What am I missing?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-01-16 : 18:03:49

You can't use the GetDateTest alias in the WHERE clause. What your WHERE clause is doing is looking for the string and not the date from your convert. You have to repeat the convert in the WHERE clause.

An aliased column can be used in an ORDER BY but not in the WHERE clause due to the order in which queries are processed. The columns in the select occur after the where clause is processed, the order by is last so the aliased column name is available by that time.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-17 : 02:18:20

In your case you just need this


select CONVERT (VARCHAR(10),date_started,101) AS 'Date Hired', convert(varchar(10),dateadd(m,-1,GetDate()),101) AS 'GetDateTest'
from tablename
WHERE date_started >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0)
AND date_started < DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,1)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

2fire
Starting Member

9 Posts

Posted - 2014-01-17 : 14:51:03

Thanks for the useful advice from both of you!

One more question, why couldn't I use this WHERE criteria to select one month previous as opposed to the example provided? -

select CONVERT (VARCHAR(10),date_started,101) AS 'Date Hired', convert(varchar(10),dateadd(m,-1,GetDate()),101) AS 'GetDateTest'
from tablename
WHERE date_started = DATEADD(mm,DATEDIFF(mm,0,GETDATE())-0,0)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-01-17 : 14:56:23

Take a look why:

select top 1000 date_started, DATEADD(mm,DATEDIFF(mm,0,GETDATE())-0,0)
from tablename

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-18 : 04:35:30

quote:
Originally posted by 2fire

Thanks for the useful advice from both of you!

One more question, why couldn't I use this WHERE criteria to select one month previous as opposed to the example provided? -

select CONVERT (VARCHAR(10),date_started,101) AS 'Date Hired', convert(varchar(10),dateadd(m,-1,GetDate()),101) AS 'GetDateTest'
from tablename
WHERE date_started = DATEADD(mm,DATEDIFF(mm,0,GETDATE())-0,0)

two reasons
1. DATEADD(mm,DATEDIFF(mm,0,GETDATE())-0,0) doesnt give previous month start date but it just gives current month start date

if you want just previous month date it should be DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0) + DAY(GETDATE())-1

2. you can use = only if your date column doesnt store a time part (ie timepart is 00:00:00).
In SQLServer if datatype is datetime then it will always have a date and a timepart so storing date part alone means timepart defaults to midnight (00:00:00). So if your field stores a valid time part like say 10:30:40 etc = operator will return false and all those records will not get returned.

see this illustration


declare @t table
(
dt datetime
)

insert @t
values('2013-12-18'),
('2013-12-18 12:10'),
('2013-11-19'),
('2013-11-07'),
('2013-11-01')
select *
from @t
WHERE dt = DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0) + DAY(GETDATE())-1

select *
from @t
WHERE dt >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0) + DAY(GETDATE())-1
AND dt <DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0) + DAY(GETDATE())


output
--------------------------------

--query 1
dt
--------------------------------
2013-12-18 00:00:00.000

--query 2
dt
---------------------------------
2013-12-18 00:00:00.000
2013-12-18 12:10:00.000

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources