selecting dates regardless of time stamp 2

[1DMF]

Hello.

I'm having trouble using WHERE mydate = 'mm/dd/yyyy' as I am also using the time part of the date/time field and so that syntax only select those records of time stamp 00:00:00

how do I say get all records matching that date and ignore time.

Cheers,

1DMF.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[gmmastros]

The best way to do this is to have 2 conditions in your where clause. The first condition has greater than or equal to the day you want, and the second condition has less than the next day. So, for today, January 23, 2009... it would look like this:

Where myDate >= '01/23/2009'
      And MyDate < '01/24/2009'

I wrote that code so that it would be clear what I am doing. However, it's best to use dates in the ISO Unseparated format, which is YYYYMMDD (so slashes, dashes, or anything else).

Where myDate >= '20090123'
      And MyDate < '20090124'

Make sense?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

lol well because the search is for an exact date and I don't need the hastle of working out what datteadd(1) is, I had already thought I would do...

where mydate >= 'yyyymmdd 00:00:00' and mydate <= 'yyyymmdd 23:59:59' , that would do the job wouldn't it ?

seems odd you can't say 'ignore time' in a simple manner.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[1DMF]

hmm well I thought the logic was sound, but it doesn't seem to be selecting any records , WHY?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[gmmastros]

Most of the time, that syntax is fine. However, the advice I gave earlier is better for a couple reasons.

If the data type of the mydate column is SmallDateTime, then 23:59:59 will round up to the next day. If you are using a DateTime column, the precision (that it shows) is in milliseconds, but there's some weirdness with it too. Suffice to say, you could end up missing some rows in your output that have a time that is in the last second of the day.

With the query I suggested, you will never get the wrong rows.

To add a day:

where mydate >= 'yyyymmdd 00:00:00' 
      and mydate < Convert(DateTime, 'yyyymmdd') + 1

Basically, convert the string to a date and then add 1. Don't forget to change the condition to 'less than' instead of 'less than or equal to'.

I know this is a minor point, and under the right circumstances, you may never get the wrong data. However, it's easy enough to modify the query to make sure it doesn't happen, ya know?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

many thanks George, your help is appreciated.

I find it bizzare that it's such a pain to do a simple query, but when did MS ever make things easy!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[jmk418]

You can also use the convert function

convert(varchar(10), getdate(), 101)

This will display 1/26/2009.

Thus

 where convert(varchar(10), mydate, 101) = '1/26/2009'

will return all records for 1/26/2009 regardless of any time attached to them

 

[1DMF]

cheers, slightly less code, but still a pain.

Having to run converts on native data because you cannot query as is, oh well , my script now runs fine, which is the main objective , so thanks for the input guys.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[gmmastros]

jmk418,

That method for stripping the time out of the data is not as good as the method I showed. If there is an index on that date column, my method will be able to use the index the filter the data faster. It will do an index seek in the execution plan. Your method will have to examine every row in the table to see if it meets the criteria. It will do an index scan.

This concept that I am describing is called [google]sargable[/google]. I encourage you to spend 10 minutes reading up about it. You'll be glad you did.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

i take it what your saying is to see if the date meets where where clause criteria, it has to convert every record in the table before doing the comparison.

Where as your method alows the data to be querried as is, without converting anything, which is less overheads.

is that right?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[gmmastros]

Yes. But more importantly...

Imagine you have a dictionary, and I ask you to show me all the words that start with the letter G. What would you do? You would flip through the pages (very quickly) and find the first word that starts with G, and the last word that starts with G and return all the rows in between.

Now, Imagine I asked you to show me all the words that contain the letter G. You would have to start at the first word and examine every word until you get to the last word in the dictionary. This would take considerably longer.

It's the same way in a database. If you use a function on a column, you will get an index scan.

For example:

Select * From YourFavoriteTable Where Id = 1233

Select * From YourFavoriteTable Where [!]Abs([/!]Id[!])[/!] = 1233

Guess which one will execute faster? Now, turn on the execution plan and run it again. In SQL Server Management Studio, press Ctrl-M (in Query Analyzer press Ctrl-K). Run it again and you will see a new table for actual execution plan. The first query will do an index seek, and the second will do a scan. Seeks are a lot faster than scans.

So, you see.... both queries return the same results, but the first executes faster. All things considered, wouldn't you want to use the faster code?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

great analogy, thanks, makes perfect sense, so the short way is not always the quick way...hmmm reminds me of my signature

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!