Datetime Select????

[sshafe]

I am doing a select from a datetime column where > '3/23/2006'. It is not returning rows that do not have a time. What is the deal?

Returns - 3/24/2006 3:33:25 PM
Does not return - 3/24/2006

Thanks in advance.

 

[gmmastros]

Try changing > to [!]>=[/!]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[sshafe]

Still doesn't work.

 

[SQLDenis]

datetime not varchar right?

also try
set dateformat mdy
for fun see if that changes things

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

Take a look at this...

Create Table #Temp(DateTimeColumn DateTime)

Insert Into #Temp
Select '3/23/2006' Union All
Select '3/24/2006' Union All
Select '3/24/2006 3:33:25 PM'

-- This returns 2 records because of the >
Select * 
From   #Temp 
Where  DateTimeColumn > '3/23/2006'

-- This returns 3 records because of the >=
Select * 
From   #Temp 
Where  DateTimeColumn >= '3/23/2006'


Drop Table #Temp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

George, the OP claims '3/24/2006' is not returned, unless it's a typo

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[brxmas]

select insertion_date = Convert(VarChar(10), mc_ad_insertion.insertion_date, 101)
From mc_ad_insertion
Where insertion_date >= '03/24/2006'

 

[SQLBill]

3/24/2006 cannot be returned from a DATETIME column. DATETIME includes the time. You could get back 3/24/2006 00:00, but you CAN'T get 3/24/2006 unless it's a VARCHAR/CHAR column and you don't store times.

*Note you CAN return just the date, but only if you CONVERT or do some other manipulation to cut off the time.

-SQLBill

Posting advice: FAQ481-4875

 

[sshafe]

Now I am totally confused....I ran the same query against another database, similiar table, same column, same datatype and IT WORKED????

Here is my query
select slot_item.* from item_master, slot_item WITH (NOLOCK)
WHERE (slot_item.sku_id=item_master.sku_id
AND slot_item.sku_id <> 0 AND item_master.whse_code = 'SE')
AND ( ((slot_item.slot_id is null)
-- and (item_master.ex_recpt_date>={d '2006-03-23'})
and (item_master.ex_recpt_date<={d '2006-04-28'})
and ( slot_item.sku_id
IN ( SELECT sku_id FROM item_cat_xref WITH (NOLOCK)
WHERE cat_code_id=8) ) ) )
ORDER BY item_master.sku_name ASC

Let me know if you need more info.

 

[vongrunt]

For starters, it would be nice to rewrite this query without ancient joins, ODBC function calls and extra ((())))'s .

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[sshafe]

I didn't write this query. It was generated from Profiler from one of our vendor supplied applications. Besides, that is not keeping the query from returning rows.

Also, I have commented out the line that is holding up returning rows.

 

[vongrunt]

OK. Is there any row returned with ex_recpt_date greater or equal 23/03/2006?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[sshafe]

no, but only because all the rows in the tables that are >= 03/23/2006 do not have a time on them. If I change the query to a date that will return rows. (our case 03/22/2006) it will return those rows, but only those rows with the time.

 

[SQLBill]

Just a question....you are using Microsoft SQL Server correct?

Double check to make sure the column IS DATETIME or SMALLDATETIME data type. Run exec sp_help table_name. Replace table_name with your table's name. Look under Column_Name for your column and then TYPE for the datatype.

If you query a DATETIME/SMALLDATETIME column, it will always return a time unless you convert it to show only the date.

-SQLBill

Posting advice: FAQ481-4875

 

[sshafe]

Results:
ex_recpt_date datetime no 8

 

[SQLBill]

Okay, just wanted to make sure. You say there's no conversion going on. But the part I'm having trouble with (which is making it hard to solve this) is that DATETIME is made up of both the date and time. If you don't enter a time, SQL Server adds the default. Same if you don't enter the date, SQL Server will add the default date. So it is stored a values equating to date AND time. Unless you are converting, it should ALWAYS return both values.

3/24/2006 00:00:00.000
NOT
3/24/2006

If you aren't converting, there's something else going on and it can't be just plain truncating the value as you say you are getting values with the date and time.

-SQLBill

Posting advice: FAQ481-4875

 

[sshafe]

I am trying to figure out what routine within the software is populating this field.

To your question. I can pull the data up through EM and that is exactly what it shows no maninipulation. Is there some kind of switch on the database that I'm unaware of that could cause this?

SELECT DISTINCT ex_recpt_date
FROM item_master
ORDER BY ex_recpt_date

( ex. result rows

3/23/2006 9:18:53 PM
3/23/2006 9:18:54 PM
3/23/2006 9:18:55 PM
3/23/2006 9:18:56 PM
3/23/2006 9:18:57 PM
3/24/2006
3/25/2006
3/26/2006

 

[SQLDenis]

Oh man...;-)

create table tt (d datetime)
insert into tt
select '3/23/2006 9:18:57 PM' union all
select '3/24/2006 0:00:00 AM'

select * from tt

Run in QA and then in Enterprise Manager and see the difference

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

You see Enterprise Manager will cut of all the zero's if the data is at midnight

So always use Query Analyzer to run your data since EM does stupid formatting all the time

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[sshafe]

I've ran in both EM and QA. Same results from both.