asked on

Select a specific datetime interval - drivin med crazy as a bat :(

Hi Experts

I have a table, which contains a DateTime field "dato"

I'd like to make aquery that returns the records for a specific time period - my problem is, that as soon as I use the time clause - it goes crazy as a bat... (or I do!)

Here's my query : SELECT *, convert(varchar, datotid, 103) as ts from xml_til_trg_cockpit WHERE knnr = '20081' and (datotid >= '2011/11/08 13:00:00' and datotid <= '2011/11/09 13:00:00' ) ORDER BY fragtnr;

What to do ?? Please help me out here - the sooner the better...

Regards,
Mark

DavidMorrison

try sticking .000 on the end of the time so 13:00:00.000

sachinpatil10d

Current query works perfectly fine.

what is the problem your getting?

trg_dk

ASKER

@DavidMorrison : Didn't change the result, but thanks!

DavidMorrison

ok so what is the issue exactly? what is the error?

trg_dk

ASKER

@Sachinpatil10d : I get no result at all - even though if I do the following query, I get 83 records:

SELECT *, convert(varchar, datotid, 103) as ts from xml_til_trg_cockpit WHERE knnr = '20081' and (CONVERT(CHAR(10),datotid,120) >= '2011-11-08' and CONVERT(CHAR(10),datotid,120) <= '2011-11-09' ) ORDER BY fragtnr;

trg_dk

ASKER

Guys sorry for not being clear : I get no result back. It started when the date turned November 1. - Only a few days before it worked fine, thus I conclude that the problem is the date format...

DavidMorrison

is it not possible that all of those 83 rows are before 13:00 on the first day and after 13:00 on the second day?

trg_dk

ASKER

@DavidM... Well... uhm I guess so... Just checking to make sure...

trg_dk

ASKER

Nope - all records are after 13:00:00 yesterday... (phew)

DavidMorrison

ok few things to look at, firstly can you print a few of those date values into here?

you need to look at your dateformat and language settings, I blogged a bit on this a little while back (http://blogs.adatis.co.uk/blogs/david/archive/2011/01/28/when-is-english-not-english.aspx)

Also what data type is your datoid column and how is the data formatted?

trg_dk

ASKER

I made a screencap from RazorSQL

I'm really not sure what it is, that you are asking me !? :O
datetime-records-EE.GIF

DavidMorrison

Ok all looks sensible.

read my blog post, that will explain how various settings in sql server affect how it treats and interprets dates. Once you understand that a little better you can look at the settings you are running with and see if it is not as you would expect.

The final question is what is the datatype of you datoid column in the table?

trg_dk

ASKER

I (tried to) read your blog - but must admit it went a bit over my level of understanding... :( So for now I'm not wiser, only more confused...

The datotid is a DateTime field. Or am I answering this wrong ??
datetime-datatype-EE.GIF

DavidMorrison

Ok no worries, run this sql, the first result set relates to the langid column from the second.

so when I run this I get 0 in the first result set, 0 is the langid value for us_english

select @@Langid
exec sys.sp_helplanguage

what do you get when you run this? pay special attention to the dateformat column as this is, by default, how sql server expects to receive dates

Thanks

Dave

lludden

Try explicitly casting the constants as dates or assign them to datetime variables.

SELECT *, convert(varchar, datotid, 103) as ts from xml_til_trg_cockpit WHERE knnr = '20081' and (datotid >= cast('2011/11/08 13:00:00' as datetime) and datotid <= cast('2011/11/09 13:00:00' as datetime) ) ORDER BY fragtnr;

As David said above, sometimes there are some settings in SQL that can cause it to interpret dates in an unexpected fashion. I typically use the format 'YYYY-MM-DD HH:mm' '2011-11-22 23:57' for instance

trg_dk

ASKER

@DavidM - I get 0 (zero)

trg_dk

ASKER

@Illuden - returns an empty result set, but thanks

DavidMorrison

ok I really cant see anything logically wrong with what you're doing.

from your original query try changing the slashes to dashes?

trg_dk

ASKER

SELECT *, convert(varchar, datotid, 103) as ts from xml_til_trg_cockpit WHERE knnr = '20081' and (datotid >= '2011-11-08 01:00:00.000' and datotid <= '2011-11-09 13:00:00.000' ) ORDER BY fragtnr;

OK now I'm getting sooo confused...

The query is supposed to run inside a JAVA workflow program called Integrator from www.ibs.com - inside Integrator it returns an empty resultset - but when I test it in Queryanalyzer, I get 83 records...

Now I'm going to kill the piece of crap program :( :(

tim_cs

You showed that there is data after 13:00 but do all those records have a knnr = 20081?

trg_dk

ASKER

@tim : yes :)

SOLUTION

DavidMorrison

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

ASKER CERTIFIED SOLUTION

Zberteoc

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

SOLUTION

Scott Pletcher

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Scott Pletcher

If that still gives you unexpected results, you definitely have another problem.

Btw, you also really should specify the schema for the table, to make sure you are referencing the correct table, for example by adding "dbo." (or whatever the schema is) before the table name:

SELECT *, convert(varchar, datotid, 103) as ts
from dbo.xml_til_trg_cockpit --<<--

trg_dk

ASKER

@Zberteoc -> Hurray it finally works :)

So the trick is to use a unix like timestamp ??!!

YYYYMMDD hh:mm:ss works in every iteration (so far ;) )

Thank you all guys!!

DavidMorrison

Your java app must be connecting to the DB with some weird old connection options is all I can say! Glad its working!

Scott Pletcher

@Zberteoc et al.
>> YYYY-MM-DD HH:MM:SS.mmm is universal format <<

That format is *NOT* universal in SQL Server. Think about it ... in a country that normally uses ydm, like 11/03/01 for Jan 3, how does SQL know you don't mean 2011-03-01 as *Jan 3* rather than Mar 1?

As *I* stated, the universal format is without the dashes.

[Yes, there are other universal formats with a "T" in them, and, IIRC, they never have any spaces in them.]