What am I doing wrong? - 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 Programming

What am I doing wrong?

Author

Topic

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2008-12-10 : 04:24:16

select * from dbo.BackupHistory
where backup_finish = Convert(char,Dateadd(d,-1,getdate()),101)
order by [size] desc

this returns 0 records. I know there are records with backup_finish date of 2008-12-09 00:15:01.000

I am wanting to pull all records dated 2008-12-09.

---------------------------

Working until "the morning sun sets the midnight sky on fire"!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-12-10 : 04:40:14

convert backup_finish too to match your calculated date exactly

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

raky
Aged Yak Warrior

767 Posts

Posted - 2008-12-10 : 04:40:27

select * from dbo.BackupHistory
where backup_finish = '12/09/2008'
order by [size] desc

or try this query

select * from dbo.BackupHistory
where backup_finish = dateadd(d,datediff(d,0,getdate()-1),0)
order by [size] desc

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-10 : 04:45:39

select * from dbo.BackupHistory
where convert(varchar(11),backup_finish ,101) = Convert(char,Dateadd(d,-1,getdate()),101)
order by [size] desc

Jai Krishna

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2008-12-10 : 04:45:52

If I do it as:

where [backup_finish] = '2008-12-09 00:55:12.000'

it returns the one record with that stamp. I want to pull all records from the prior date of when the job is run.

---------------------------

Working until "the morning sun sets the midnight sky on fire"!

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2008-12-10 : 04:52:15

quote:
Originally posted by Jai Krishna

select * from dbo.BackupHistory
where convert(varchar(11),backup_finish ,101) = Convert(char,Dateadd(d,-1,getdate()),101)
order by [size] desc

Jai Krishna

That did it. Thanks...

---------------------------

Working until "the morning sun sets the midnight sky on fire"!

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-10 : 05:04:57

Welcome

Jai Krishna

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-10 : 05:22:53

where DATEDIFF(DAY, backup_finish, getdate()) = 0

E 12°55'05.63"
N 56°04'39.26"

raky
Aged Yak Warrior

767 Posts

Posted - 2008-12-10 : 11:11:08

quote:
Originally posted by Peso

where DATEDIFF(DAY, backup_finish, getdate()) = 0

E 12°55'05.63"
N 56°04'39.26"

He wants records date with '2008-12-09'

hence

where DATEDIFF(DAY, backup_finish, getdate()-1) = 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-10 : 11:15:25

quote:
Originally posted by Jai Krishna

select * from dbo.BackupHistory
where convert(varchar(11),backup_finish ,101) = Convert(char,Dateadd(d,-1,getdate()),101)
order by [size] desc

Jai Krishna

better not to convert dates to varchar

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-10 : 11:18:35

[code]select * from dbo.BackupHistory
where backup_finish >= dateadd(d,datediff(d,0,getdate()),-1)
and backup_finish < dateadd(d,datediff(d,0,getdate()),0)
order by [size] desc[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources