[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

Date and Time in MS SQL

Hello,

I'm running this:

select ..... where Create > CONVERT(DATETIME, CONVERT(INT, GETDATE() -1)))

Create = Field Name

I'm getting June 28 and June 27... but I should get only June 27

I even tried this: CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE()-1 ))
and
CONVERT(DATETIME, CONVERT(INT, GETDATE() -1))

but I'm not getting only for June 27....    -1 should take me a day back..no? but it doesn't... can anyone help me with this please

Thanks


0
ezzadin
Asked:
ezzadin
  • 2
  • 2
2 Solutions
 
skacoreCommented:
Hello,
  You may want to try

SELECT .... WHERE create > DATEADD(d, -1, GETDATE())

If you just want stuff from a day ago, it's a little cleaner.
0
 
ezzadinAuthor Commented:
still the same.... I'm getting some of June 27 and some of June 28

the first record start    2006-06-27 17:50:08.000

I have some records that are 2006-06-27 00:00:00.000 ... i should get those as well and not get June 28

Please help

Thanks

0
 
skacoreCommented:
Ah, now I see, try this:

SELECT ....  create >= DATEADD(dd, -1, DATEDIFF(dd, 0, GETDATE())) and create < DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
0
 
MyersACommented:
Hi ezzadin,

When I compare dates, I usually get rid of the time and I only use the date part. You can try this:
SELECT .... WHERE
create >= convert(DateTime,convert(varchar(11),getdate()-1)) and       
create < convert(DateTime,convert(varchar(11),getdate()))


0
 
ezzadinAuthor Commented:
Thanks guys
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now