GetDate( ) Used to query results 1

[jcg6]

I want a query that displays all results entered on that day. This date is entered into the database via a GetDate()default value.

Now that I want to get those results back out...I am having trouble because of the time that is affixed with the date in the field. I just want to grab results based on the date - not the time.

I'm sure that there is some type of formatting I can do I my query, but I am having troubles. Any help would be appreciated. Thanks.

 

[SQLSister]

USe between

where datefield between 1/2/2004 00:00:00 and 1/2/2004 23:59:59

Questions about posting. See faq183-874

 

[jcg6]

I want to be able to use this query every day, so I don't want to have to type in a new date every time. Is there anyway I can format the GetDate() to grab values on the date only??

 

[amorous]

If you want to do a string Manipulation...Here is what you have to do

Dim newdate

newdate=Split(yourdatefield,"") <- we are splitting your date field at the Space

NOW

newdate(0) contains 1/2/2004
and
newdate(1) contains 00:00:00

Then you can do the following query...

Where yourdatefield between newdate1(0) and newdate2(0)

-VJ

 

[TJRTech]

Might I suggest adding some "handy" user defined functions to your database, one for taking a datetime and returning its TwelveAM counterpart and another for returning the just prior to Midnight counterpart.

See this thread:

thread183-805128

Then, you can easily query:

where datefield between dbo.TwelveAM(GetDate()) and dbo.Midnight(GetDate())

 

[jdgonzalez]

You could try something

where yourdatefield = convert(char(10), getdate(), 101)

Assuming that yourdatefield does not have a time component. If it does you'll just have to use the convert function to eliminate the time component.

 

[vongrunt]

This trick is OK if yourdatefield values have time fraction set to 0:00:00. Otherwise both left and right values must be converted to char(10)/101.

 

[vongrunt]

Sorry jdgonzales, I obviously repeated your words

What about datediff(dd, yourdatefield, getdate()) = 0?

 

[john76]

After running this query:

Declare @MyDate datetime
Set @MyDate = GetDate()

Select DateTimeValue = @MyDate,
       DateOnlyValue = Convert(datetime, Convert(varchar, @MyDate, 101)),
       DateBegin = Convert(datetime, Convert(varchar, @MyDate, 101)),
       DateEnd = Convert(datetime, Convert(varchar, DateAdd(day, 1, @MyDate), 101))

It occurs to me that 1) If you are wanting the results for the same day that the query is executed, you could put something like:

...
Where  MyDateField >= Convert(datetime, Convert(varchar, GetDate(), 101))

Whereas if you are wanting to select a date range, such as yesterday's data, you could use:

Where  MyDateField >= Convert(datetime, Convert(varchar, DateAdd(day, -2, GetDate())))
  And  MyDateField < Convert(datetime, Convert(varchar, DateAdd(day, -1, GetDate())))

HTH,
John

 

[jcg6]

Thanks to everyone for all of your helpful hints. I was able to get it to work for my purposes!!

 

[ESquared]

Just out of curiosity, will the two GetDate() functions in john76's last code block *always* evaluate to the same day?

If they are actually evaluated twice, it's possible the first could be 7/12/2004 23:59:59.997 and the next one 7/13/2004 0:0:0.000.

Just curious!

 

[john76]

E[sup]2[/sup],

True, but if this is a problem then it would be easy to declare a datetime variable (such as I did in the first query) which is set to GetDate() and then use that variable in the actual calculations. This would ensure that the date band was calculated using exactly the same datetime value. In practice however, I doubt that anyone would actually run the query so precisely close to midnight that it would make any real difference.

John

 

[TJRTech]

No, the query processor will make all GetDate() expressions in a single statement have the same value.

TR

 

[john76]

TJRTech,

I did not know that, cool! E[sup]2[/sup], I guess the variable declaration is unneccessary afterall! ;-)

John