By excelguy


2019-07-11 19:02:19 8 Comments

I am trying to use my query as the current day for my SQL query.

What I am trying below returns 0 records:

select * 
from [TEST].[dbo].LIMIT 
where endOfDay = GETDATE()

select * 
from [TEST].[dbo].LIMIT 
where endOfDay = dateadd(dd, datediff(dd, 0, getdate()), 0)

where endOfDay >= getdate() and endOfDay < getdate() + 1 

An example date would be 2019-07-09 00:00:00.0

Also if there is no given date for today's date can I get a range of dates?

Any help would be appreciated, thanks.

5 comments

@Raging Bull 2019-07-11 19:17:03

Try this:

select * 
from [TEST].[dbo].LIMIT 
where cast(endOfDay as date) = cast(GETDATE() as date)

SQL Fiddle to see what the date will be compared with.

@excelguy 2019-07-11 19:18:00

syntax error near =

@Raging Bull 2019-07-11 19:19:10

@excelguy: Please check the sql fiddle link to see the comparison.

@excelguy 2019-07-11 19:21:06

hmm for smoe reason my database is giving me an incorrect syntax near =

@Raging Bull 2019-07-11 19:23:02

@excelguy: Please check the query you have tried in your system. Did you miss any paranthesis? Also, it is a good way to cast the date field also, it will make sure that there are no hours/minutes/seconds.

@excelguy 2019-07-11 19:27:49

okay syntax error was on my side. Cant seem to get it to return any records though.

@Peter B 2019-07-11 19:12:41

First, you need to omit the quotes or else 'GETDATE()' is just a string.

Second, to match by "day", you need to strip the time part from the result of GETDATE(), which you can do by using CAST(GETDATE() as DATE) instead:

select *
from [TEST].[dbo].LIMIT
where endOfDay = CAST(GETDATE() as DATE)

For this to work best, the endOfDay column also needs to be of type DATE. If it is something else, you need to also CAST or CONVERT endOfDay to a DATE.

@Alex Kudryashev 2019-07-11 19:13:41

It is good idea to cast(endOfDay as date) as well.

@Barbaros Özhan 2019-07-11 19:04:27

Using quotes makes it looking for a meaningless literal 'GETDATE()' to find nothing

You can use

with LIMIT(today, EndOfDay) as
( 
select CONVERT(DATETIME, CONVERT(date, GETDATE())), GETDATE()
)
select EndOfDay 
  from LIMIT 
 where EndOfDay >= today and EndOfDay < today + 1;

Demo

@excelguy 2019-07-11 19:05:50

Thanks, but this doesnt seem to return anything either. Perhaps I have no data on this date, can I subtract a day or two?

@Barbaros Özhan 2019-07-11 19:08:20

@excelguy welcome, is this OK now ?

@excelguy 2019-07-11 19:10:34

This should give a range of dates right? Not sure, nothing is returning but the query is being executed.

@excelguy 2019-07-11 19:16:59

doesnt seem to work using both your suggestions.

@excelguy 2019-07-11 19:31:51

WHOA its working!! Can you explain this? Can you Select all on this?

@Barbaros Özhan 2019-07-11 19:37:47

@excelguy CONVERT(DATETIME, CONVERT(date, GETDATE())) returns current date and time, and GETDATE() returns only current day. Here we're looking for the interval for current day.

@Barbaros Özhan 2019-07-11 19:40:04

@excelguy nothing, just a standard traditional adornment :)

@excelguy 2019-07-11 19:42:04

when i remove id i get an error that my LIMIT has more columns that were specified in the column list

@Barbaros Özhan 2019-07-11 19:42:50

@excelguy since, you need to remove 1,(inside the subquery) also.

@excelguy 2019-07-11 19:46:54

@jabs 2019-07-11 19:18:02

Assuming you don't care about time, convert both EndofDay and Getdate to a simple date for comparison.

select * from [TEST].[dbo].LIMIT where convert(date,EndOfDay) = convert(date,getdate())

@excelguy 2019-07-11 19:18:48

not returning anything :(

@SMor 2019-07-11 19:38:09

@excelguy OK - no one can really help if you can't provide useful feedback and you provide a single example for a date that is irrelevant. Help others help you. If you don't have tsql skills, let people know so that explanations and discussions provide appropriate details. So post the results (in your original question) of this query: select top 5 * from Test.dbo.LIMIT order by endOfDay desc;

@Scott Hoffman 2019-07-11 19:11:35

GETDATE() returns the server's current date and time. Unless you have any records that match that exactly, it doesn't make sense to use GETDATE. It doesn't look like you do based on the endOfDay column name.

The other criteria you show, dateadd(dd,datediff(dd,0,getdate()),0) is essentially stripping the time off and returning midnight of the current date.

select getdate()                                2019-07-11 15:10:09.287
select dateadd(dd,datediff(dd,0,getdate()),0)   2019-07-11 00:00:00.000 

@John Cappelletti 2019-07-11 19:12:42

Why not simply convert(date,getdate()) ?

@D Stanley 2019-07-11 19:14:26

@JohnCappelletti backwards compatibility - DATE was added in SQL Server 2008.

@Scott Hoffman 2019-07-11 19:16:25

@JohnCappelletti that would work too, I was just trying to explain the differences the way the OP had asked it.

@John Cappelletti 2019-07-11 19:18:03

@ScottHoffman I get that, I just seemed an odd route go go :)

@John Cappelletti 2019-07-11 19:20:41

@DStanley Bit of a reach, but I'll buy it :)

Related Questions

Sponsored Content

43 Answered Questions

[SOLVED] How to return only the Date from a SQL Server DateTime datatype

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

40 Answered Questions

[SOLVED] Parameterize an SQL IN clause

11 Answered Questions

[SOLVED] INNER JOIN ON vs WHERE clause

37 Answered Questions

28 Answered Questions

[SOLVED] Finding duplicate values in a SQL table

  • 2010-04-07 18:17:29
  • Alex
  • 2484675 View
  • 1687 Score
  • 28 Answer
  • Tags:   sql duplicates

25 Answered Questions

[SOLVED] How do I perform an IF...THEN in an SQL SELECT?

45 Answered Questions

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

4 Answered Questions

[SOLVED] Inserting multiple rows in a single SQL query?

Sponsored Content