Query failis

HAI Friends
I need some info.
I have written a query which was working fine with DB user id and password deticated to this DB which i am using.
Then we change the login mode to Windows Login with Trusted connection. After this change my query is failing.
My query is which working in SQL login mode is
SELECT DATE_TIME,CARDNO, NATIONALID, DOB, STATUS FROM TBL_INFO where DATE_TIME between '29/12/2003 00:00:01' and '30/12/2003 23:59:59' Order by DATE_TIME ASC

Where DATE_TIME is date time Data type.

i change this query to check with Windows login with trusted connection.
SELECT DATE_TIME,CARDNO, NATIONALID, DOB, STATUS FROM TBL_INFO where DATE_TIME between convert(datetime, '29/12/2003', 103 ) and convert(datetime, '30/12/2003', 103 ) Order by DATE_TIME ASC
No Error is mention for this query but no records are fetched. But data is there in DB.
Please help i am new to SQL server.
Expecting your replies ASAP
Signing Off
Venkatesh.VHas the windows login been given access to the database/tables ? Where are you running these queries ? Do you have access to query analyzer ?|||Originally posted by rnealejr
Has the windows login been given access to the database/tables ? Where are you running these queries ? Do you have access to query analyzer ?

Yes i have access to Tables. i have been given DB owner rights.

i have to run these queries form Visual basic. i have to populate these data as reports using Crystal reports. Query is not working even in Query Analyser.
i ma trying it out with 2 diff login id with query analyser|||In query analyzer - what about select * from tbl_info/select count(*) from tbl_info. Also, what message is returned in query analyzer ?|||You connected to the server?

Then you're in...

And I bet you're trying to execute the code in master...|||But he would get invalid object name if that were the case ...|||Originally posted by rnealejr
In query analyzer - what about select * from tbl_info/select count(*) from tbl_info. Also, what message is returned in query analyzer ?
Dear Friend,
I ma able to see data if i put a Select * Statement and i ma able to get count etc..
Only thing this query fails ..|||It shoulds like your where clause is hosed - leave off the where clause and try again. If that returns rows, what values are stored in the date_time field ?|||First:

You need to post the error message. We're just guess right now as to what the problem is.

Second:

You need to post the Query and The DDL of the Tables

Third:

Sample Data would be a BIG help|||Originally posted by Brett Kaiser
First:

You need to post the error message. We're just guess right now as to what the problem is.

Second:

You need to post the Query and The DDL of the Tables

Third:

Sample Data would be a BIG help

Dear Frnd,
I have ran the query with out any condtion. it works. Even with one condtion by checking only first date value it works. when we put the between caluse in the query it returns nothing.

i have attched the table strucure,index,constraints and sample data. please have look into this and help me out.|||You seem to be focusing on the fact that you changed your login method, but your post indicates that you also changed your query, and this is likely the problem. For one thing,

between '29/12/2003 00:00:01' and '30/12/2003 23:59:59'

is NOT the same as

between convert(datetime, '29/12/2003', 103 ) and convert(datetime, '30/12/2003', 103 )

At the very least, the second statement covers 24 fewer hours than the first statement, so any data on 30/12/2003 would not show up.

Why did you switch to using a convert function?

blindman|||Originally posted by blindman
You seem to be focusing on the fact that you changed your login method, but your post indicates that you also changed your query, and this is likely the problem. For one thing,

between '29/12/2003 00:00:01' and '30/12/2003 23:59:59'

is NOT the same as

between convert(datetime, '29/12/2003', 103 ) and convert(datetime, '30/12/2003', 103 )

At the very least, the second statement covers 24 fewer hours than the first statement, so any data on 30/12/2003 would not show up.

Why did you switch to using a convert function?

blindman

As my Previous Query was not working under Trusted conenction it throws me error stating that "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
So to solve this i tired with Convert fucntion. Still after that no records are fetched. But data is there in Table.|||If you show what is returned in the date_time field, I think we can solve this. Also, what is the datatype for the date_time field - datetime or smalldatetime ?|||Originally posted by rnealejr
If you show what is returned in the date_time field, I think we can solve this. Also, what is the datatype for the date_time field - datetime or smalldatetime ?

Datatype for DATE_TME is datetime.
when we select date_time alone we get data as
30/12/2003 11:01:10
30/12/2003 10:34:18
30/12/2003 10:34:01|||I looked at your attachment...and it does look like the column is defined as datetime...

Your data however doesn't...

Do this and tell us what you get

[grasping at straws now]

SELECT COUNT(*) FROM TBL_INFO WHERE ISDATE(DATE_TIME) = 0

If you get anything other than 0, you'll have non date data...

Which is what your error message is telling you...but not what your table def shows

[/grasping at straws now]

Also try DBCC CHECKTABLE ('TBL_INFO')

And let use know what that says...|||Maybe I missed it earlier in the thread but it seems to me as though your date format is wrong.

Try changing your between clause to the following:

between '2003-12-29 00:00:01' and '2003-12-30 23:59:59'

That should work.

Dan|||Not sure if I smell a discrepancy in regional settings here or not, but...

If you run sp_helpdb on your database, what is the collation (under status)? (example: sp_helpdb pubs)

Can you re-write the query without the between, and have it work? As in

where date_time > '29/12/2003 00:00:01'
and date_time < '30/12/2003 23:59:59'

If it works this way, great, but if you get no error and no records, try lowering the bottom constraint ('29/12/2003 00:00:01'), until something gets in range.

If you get no errors, then the query parses and compiles correctly. Maybe you just have a condition like where 1 = 2...|||Oh, and what does the date format look like on the client computer?|||Originally posted by Brett Kaiser
I looked at your attachment...and it does look like the column is defined as datetime...

Your data however doesn't...

Do this and tell us what you get

[grasping at straws now]

SELECT COUNT(*) FROM TBL_INFO WHERE ISDATE(DATE_TIME) = 0

If you get anything other than 0, you'll have non date data...

Which is what your error message is telling you...but not what your table def shows

[/grasping at straws now]

Also try DBCC CHECKTABLE ('TBL_INFO')

And let use know what that says...

When i ran the query
SELECT COUNT(*) FROM TBL_INFO WHERE ISDATE(DATE_TIME) = 0
It returns 0
i rean this query in two different logins such as DB login id and win user
the results are same.
After this i ran the DBCC CHECKTABLE ('TBL_INFO')
it returns
/*----------
DBCC CHECKTABLE ('TBL_INFO')
----------*/
DBCC results for 'TBL_INFO'.
There are 11 rows in 2 pages for object 'TBL_INFO'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

i ran these queries from Query Analyser.

Can you please tel me wat these commands will do
"DBCC check table".|||Originally posted by danielacroft
Maybe I missed it earlier in the thread but it seems to me as though your date format is wrong.

Try changing your between clause to the following:

between '2003-12-29 00:00:01' and '2003-12-30 23:59:59'

That should work.

Dan
Date Format is same dd-mm-yyyy.|||Originally posted by MCrowley
Not sure if I smell a discrepancy in regional settings here or not, but...

If you run sp_helpdb on your database, what is the collation (under status)? (example: sp_helpdb pubs)

Can you re-write the query without the between, and have it work? As in

where date_time > '29/12/2003 00:00:01'
and date_time < '30/12/2003 23:59:59'

If it works this way, great, but if you get no error and no records, try lowering the bottom constraint ('29/12/2003 00:00:01'), until something gets in range.

If you get no errors, then the query parses and compiles correctly. Maybe you just have a condition like where 1 = 2...

Status is
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1253_CI_AS, SQLSortOrder=114, IsTornPageDetectionEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics

I even tried that it returns 0 rows. Where data was there in DB.|||What is the result if you do the between using (no convert/no time):

'mm/dd/yyyy' and 'mm/dd/yyyy'
OR this method
'dd/mm/yyyy' and 'dd/mm/yyyy'|||Originally posted by rnealejr
What is the result if you do the between using (no convert/no time):

'mm/dd/yyyy' and 'mm/dd/yyyy'
OR this method
'dd/mm/yyyy' and 'dd/mm/yyyy'

Result is "No rows are returned."|||What the heck is ""???

Those aren't even letter "i's. There a different ASCII character altogether. Could that be some weird regional time format for am/pm? Not that I can find.

Please run this query IN QUERY ANALYZER and post the results:

SELECT distinct top 20
getdate() SystemDate,
DATE_TIME as RawFormat,
convert(varchar(19), DATE_TIME, 120) as Format120,
cast(convert(varchar(19), DATE_TIME, 120) as datetime) as Reconverted
FROM TBL_INFO

blindman|||...and please run this query as well, through QUERY ANALYZER (Crystal frequently mucks things up, especially field formats).

SELECT DATE_TIME
FROM TBL_INFO
where DATE_TIME between '12/29/2003 00:00:01' and '12/30/2003 23:59:59'

blindman|||Originally posted by blindman
What the heck is ""???

Those aren't even letter "i's. There a different ASCII character altogether. Could that be some weird regional time format for am/pm? Not that I can find.

Please run this query IN QUERY ANALYZER and post the results:

SELECT distinct top 20
getdate() SystemDate,
DATE_TIME as RawFormat,
convert(varchar(19), DATE_TIME, 120) as Format120,
cast(convert(varchar(19), DATE_TIME, 120) as datetime) as Reconverted
FROM TBL_INFO

blindman

Sir,
I ran the query it returns
2/1/2004 10:16:10 1/1/2004 7:42:52 2004-01-01 19:42:52 1/1/2004 7:42:52
2/1/2004 10:16:10 1/1/2004 7:46:22 2004-01-01 19:46:22 1/1/2004 7:46:22
2/1/2004 10:16:10 1/1/2004 7:51:06 2004-01-01 19:51:06 1/1/2004 7:51:06
2/1/2004 10:16:10 1/1/2004 7:51:33 2004-01-01 19:51:33 1/1/2004 7:51:33
2/1/2004 10:16:10 1/1/2004 7:55:14 2004-01-01 19:55:14 1/1/2004 7:55:14
2/1/2004 10:16:10 1/1/2004 7:56:40 2004-01-01 19:56:40 1/1/2004 7:56:40
2/1/2004 10:16:10 1/1/2004 7:59:07 2004-01-01 19:59:07 1/1/2004 7:59:07
2/1/2004 10:16:10 1/1/2004 8:19:16 2004-01-01 20:19:16 1/1/2004 8:19:16
2/1/2004 10:16:10 1/1/2004 8:28:54 2004-01-01 20:28:54 1/1/2004 8:28:54
2/1/2004 10:16:10 1/1/2004 8:31:33 2004-01-01 20:31:33 1/1/2004 8:31:33
2/1/2004 10:16:10 1/1/2004 8:41:35 2004-01-01 20:41:35 1/1/2004 8:41:35
2/1/2004 10:16:10 1/1/2004 10:16:58 2004-01-01 22:16:58 1/1/2004 10:16:58
2/1/2004 10:16:10 1/1/2004 10:27:03 2004-01-01 22:27:03 1/1/2004 10:27:03
2/1/2004 10:16:10 1/1/2004 11:14:09 2004-01-01 23:14:09 1/1/2004 11:14:09
2/1/2004 10:16:10 1/1/2004 11:14:14 2004-01-01 23:14:14 1/1/2004 11:14:14
2/1/2004 10:16:10 1/1/2004 11:22:42 2004-01-01 23:22:42 1/1/2004 11:22:42
2/1/2004 10:16:10 2/1/2004 3:11:32 2004-01-02 15:11:32 2/1/2004 3:11:32
2/1/2004 10:16:10 2/1/2004 3:11:47 2004-01-02 15:11:47 2/1/2004 3:11:47
2/1/2004 10:16:10 2/1/2004 3:14:30 2004-01-02 15:14:30 2/1/2004 3:14:30
2/1/2004 10:16:10 2/1/2004 3:22:05 2004-01-02 15:22:05 2/1/2004 3:22:05

"" means PM in Greek. As regional settings is set to Greece,
"" means AM in Greek.|||Originally posted by blindman
...and please run this query as well, through QUERY ANALYZER (Crystal frequently mucks things up, especially field formats).

SELECT DATE_TIME
FROM TBL_INFO
where DATE_TIME between '12/29/2003 00:00:01' and '12/30/2003 23:59:59'

blindman
I ran the query ,
I got the Date time filed value.
Since data has been purged,as it was 30 days old. i have data from 1st alone.

Query is working in both login .. i mean in with DB login and as well as Nt login.
Still i am not clear how come same query was failing last week and now it working in 2 login id woth out nay change?

Last week it was thorwing errro stating that can't convert the char to datetime with NT login id , when i use the convert func.. it was not returning any rows.

but Today query without any convert function is working in both login id .. with DB user id and NT user login

Do u get anything froM this ??/|||There was a change.

The old code was:
between '29/12/2003 00:00:01' and '30/12/2003 23:59:59'

...and the new code is:
between '12/29/2003 00:00:01' and '12/30/2003 23:59:59'

Notice the change in dateformat from dd/mm/yyyy to mm/dd/yyyy.

There is some confusion going on, either within SQL Server or through Crystal reports, when exchanging time formats. Crystal is notoriously unreliable about such things.

If you can use the 'mm/dd/yyyy' format, then that should solve your problem. Better is to use format 120 "yyyy-mm-dd hh:mi:ss(24h)" which is supposedly interpreted correctly across all instances of SQL Server.

blindman|||Originally posted by blindman
There was a change.

The old code was:
between '29/12/2003 00:00:01' and '30/12/2003 23:59:59'

...and the new code is:
between '12/29/2003 00:00:01' and '12/30/2003 23:59:59'

Notice the change in dateformat from dd/mm/yyyy to mm/dd/yyyy.

There is some confusion going on, either within SQL Server or through Crystal reports, when exchanging time formats. Crystal is notoriously unreliable about such things.

If you can use the 'mm/dd/yyyy' format, then that should solve your problem. Better is to use format 120 "yyyy-mm-dd hh:mi:ss(24h)" which is supposedly interpreted correctly across all instances of SQL Server.

blindman
So How does this Query to be changed to format 120

SELECT DATE_TIME, CARDNO, MFLAG, XFLAG, INFO FROM TBL_TRANS where Menuid = 3 and DATE_TIME between '02/01/2004 00:00:01' and '02/01/2004 23:59:59' Order by DATE_TIME ASC

can u pls help me out on this|||SELECT convert(varchar(20),DATE_TIME, 120),CARDNO, MFLAG, XFLAG, INFO FROM TBL_TRANS where Menuid = 3 and DATE_TIME between '02/01/2004 00:00:01' and '02/01/2004 23:59:59' Order by DATE_TIME ASC|||Originally posted by rnealejr
SELECT convert(varchar(20),DATE_TIME, 120),CARDNO, MFLAG, XFLAG, INFO FROM TBL_TRANS where Menuid = 3 and DATE_TIME between '02/01/2004 00:00:01' and '02/01/2004 23:59:59' Order by DATE_TIME ASC

Query is not returning any rows.

But when i run this query with date range from 1 to 2
SELECT convert(varchar(20),DATE_TIME, 120)as DATE_TIME,CARDNO, MFLAG, XFLAG, INFO FROM TBL_TRANS where Menuid = 3 and
DATE_TIME between '01/01/2004 00:00:01' and '02/01/2004 23:59:59' Order by DATE_TIME ASC

it returns all rows of date 1 and 2 ..

And Also when i run it as
SELECT DATE_TIME, CARDNO, MFLAG, XFLAG, INFO FROM TBL_TRANS where Menuid = 3 and
DATE_TIME between '01/01/2004' and '03/01/2004' Order by DATE_TIME ASC
it returns rows which have date 2/1/2004

Any idea wat cud be the problem
Thanking You|||Originally posted by rnealejr
SELECT convert(varchar(20),DATE_TIME, 120),CARDNO, MFLAG, XFLAG, INFO FROM TBL_TRANS where Menuid = 3 and DATE_TIME between '02/01/2004 00:00:01' and '02/01/2004 23:59:59' Order by DATE_TIME ASC

The above query returns all rows of Date 1st alone with
DB login and not returing any rows with NT trusted connection.

Also this query
SELECT DATE_TIME, CARDNO, MFLAG, XFLAG, INFO FROM TBL_TRANS where Menuid = 3 and DATE_TIME between '02/01/2004 00:00:01' and '02/01/2004 23:59:59' Order by DATE_TIME ASC

Is working with DB login id
and not with NT Login id with trusted connection ..
This is my problem ..
This query returns all rows of 2nd alone|||Originally posted by rnealejr
SELECT convert(varchar(20),DATE_TIME, 120),CARDNO, MFLAG, XFLAG, INFO FROM TBL_TRANS where Menuid = 3 and DATE_TIME between '02/01/2004 00:00:01' and '02/01/2004 23:59:59' Order by DATE_TIME ASC

I think i got it

i change the date format to yyyy-mm-dd for the above query..
it is working with nt trusted conenction this wat i need ..

let me check all of my queris and i shall get back to u ASAP..
Thanking you all for ur efforts|||SELECT DATE_TIME, CARDNO, MFLAG, XFLAG, INFO
FROM TBL_TRANS
where Menuid = 3
and
DATE_TIME between '2004-02-01 00:00:01' and '2004-02-01 23:59:59'
Order by DATE_TIME ASC

...should be interpreted by the sql server unambiguously. I don't think you need to do an explicit convert for your situation (maybe you do), but the datestrings in the above example are already in format 120.

The question is, can you get Crystal to submit them in this format?

You haven't talked much about how your process works, but I hope that you put this code into a stored procedure, and then just have crystal call the stored procedure and submit the datetime values as datetime values (not as strings).

blindman|||Can't you call a sproc from Crystal?|||Yes, Crystal will reference stored procedures. They don't appear on the list of data resources by default. You have to go into options on the dialog box and specify that you want the listed. Just one more minor way the crystal sucks.

Have I mentioned before that Crystal sucks?

It does.

blindmansql