Date Conversion Problem 1

[nomi2000]

I hava a table say TableA,below is the data in it
the date column is MYDATE,but when i am quering this
with this query its not returning the desired result,Any idea what i am doing wrong?
Select * from TableA
Where Convert(varchar,[MYDATE], 103)) between Convert(varchar,'01/14/2004',103) and Convert(varchar,'01/15/2004',103)
MYDATE ACTION
2004-01-30 10:53:03.263 Precedent approval review required
2004-01-17 00:00:00.000 Precedent approval review required
2004-01-16 00:00:00.000 Blueprints requested from engineering
2004-01-20 00:00:00.000 Client letter of credit pending
2004-01-16 00:00:00.000 Letter of explanation approvals
2004-03-10 14:38:11.373 Need Technical Spec.
2004-03-15 09:53:29.670 Blueprints
2004-03-29 09:57:50.327 Request Blueprints
2004-04-13 13:05:44.500 2121
2004-04-15 08:47:13.873 Letter of Intent
2004-04-15 14:41:25.450 87878
2004-04-21 16:15:21.013 4578
2004-04-21 15:56:18.217 89898
2004-04-21 16:06:35.107 3434
2004-04-22 10:39:41.750 211212
2004-04-23 14:56:09.403 465465
2004-04-26 16:55:40.390 VL Test email to Brenda
2004-04-26 14:24:36.983 13232
2004-04-26 16:26:12.293 478
2004-04-26 16:06:21.843 788787
2004-04-23 11:53:27.420 Please Supplie Tech Spec
2004-04-30 10:22:37.730 complete
2004-07-26 16:14:38.903 565
2004-07-26 11:57:04.810 Test Action Item


Nouman Zaheer
Software Engineer
MSR

 

[cLFlaVA]

Why not:

SELECT *
FROM TableA
WHERE [MYDATE] BETWEEN #01/14/2004# AND #01/15/2004#

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[JohnDTampaBay]

Possibly because styles (for dates) are only used when converting FROM datetime or smalldatetime TO char or varchar.

What result are you getting and why is it incorrect? From the data provided, I don't see any rows that meet your criteria.

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[john76]

I'm a bit leary of performing comparisons using Between, <, <=, >, and >= on character data. If what you are trying to accomplish is to select all records where the MYDATE field is (using your example) equal to January 14, 2004 or January 15, 2004 then you might try:

Select  *
  From  TableA
 Where  MYDATE Between '2004-01-14 00:00:00.000' And '2004-01-15 23:59:59.999'

When you type the datetime values without specifying the time portion, SQL Server automatically appends a time of 00:00:00.000 to each, so you need to specify the time to make sure you are getting everything you need into the range.
If you don't want to specify the times explicitly, the following code will work exactly the same:

Select  *
  From  TableA
 Where  MYDATE >= '2004-01-14'
   And  MYDATE < '2004-01-16'

HTH,
John

 

[nomi2000]

Hi Guys
Thanks for quick response but every suggestion except on of the suggestiobn by Join is working
a)John ur suggestion for
Select * from TableA
Where [MYDATE] between '2004-03-15 00:00:00.000' And '2004-03-15 23:59:59.999'
is working but the problem is that i am passing Date from FRONT-END & it dosen't containt the hh:mm:ss:ms part,Is it possible to convert it to some datetime format which could be in MM/DD/YY style which is passed from Front-End and then check this at back-end?

b) Sorry JohnDTampaBay you can see i miswrote the date criteria but above query also wont returning me result for the dates between 14 and 15 of March although its present in data

c) Select * from TableA
Where [MYDATE] between #03/14/2004# and #03/15/2004#
giving me error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '#'.

Nouman Zaheer
Software Engineer
MSR

 

[cLFlaVA]

Instead of the #, use '

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[ESquared]

john76,
'2004-01-15 23:59:59.999' is equivalent to '2004-01-16 00:00:00.000' because datetime is only accurate to 1/300th of a second, not 1/1000th, and this is handled by only allowing certain millisecond numbers. The highest allowed is 997 so you would need to use '2004-01-15 23:59:59.997'. The engine does some method of alternating between 2 and 3 steps to the next allowed number, it's all in Books Online if you are interested.

Second, specifying the time with 00:00:00.000 is totally unnecessary, it is exactly identical to the date without the time.


cLFlaVA,
Pound signs are Microsoft Access/VB date literal format. SQL server uses single quotes/regular char strings.


nomi2000,
Instead of converting things to character format and then comparing, I suggest you instead convert the time passed from the front-end to a date format and then compare. Comparing varchar will NOT give you the results you want unless you pick a format that puts the year first. In strings, the following is true:

'01/01/2047' is less than
'06/01/2003' which is less than
'12/01/1999'

I concur with john76's last code block as the best method,

Select  *
  From  TableA
 Where  MYDATE >= '2004-01-14'
   And  MYDATE < '2004-01-16'

And pay special attention to how the first comparison is >= and the second is <. This is required to include 12:00 am on the 14th, but exclude it on the 16th, giving you ever date (+time) that falls on the 14th or the 15th. Not doing a conversion on MYDATE means that an index could be used, and the query engine doesn't have to do a conversion on every row in your entire table.








-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[nomi2000]

Thanks Esquared,John and everyone
Your help is giving me light in the dark As far as the last query giving by Esquare
Select *
From TableA
Where MYDATE >= '2004-01-14'
And MYDATE < '2004-01-16'
the query is working but with only one problem
say if i want to filter out the date on the particular date say 2004-01-15 then it will not give correct result say
Select *
From TableA
Where MYDATE >= '2004-01-15'
And MYDATE < '2004-01-15'
i also tried
Select *
From TableA
Where MYDATE >= '2004-01-15'
And MYDATE <= '2004-01-15'
but still no data is coming from any particular date although its present in the table
Thanks again
Nouman


Nouman Zaheer
Software Engineer
MSR

 

[cLFlaVA]

Think about the logic-

How could a date be before, equal to, AND after one specific date?

If you want the data for all dates OTHER THAN 2004-01-15, just use

WHERE MYDATE <> '2004-01-15'

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[ESquared]

cLFlaVA,

I don't think this will give the results you want. If MYDATE is a datetime field with times attached to the dates, then the construction you selected is equivalent to:

WHERE MYDATE <> '2004-01-15 00:00:00.000'

which won't exclude very many records!


nomi2000,

The thing that is going on here is that datetime fields have times attached to them. For example, '1/1/1900 12pm' is literally stored in SQL Server as 0.5. This means, 0.5 days since the arbitrary first date. My server right now returns from GetDate() the value '2004-08-05 09:11:18.140' which is stored internally as 38202.382848842593. That is, 38,202 days since '1/1/1900' and about 38/100ths of the way through one day, which happens to equal 9:11:18.140 am.

Let's say you were given the following list:

[tt] X [/tt]
[tt]0.5
0.7
1.0
1.2
1.5
1.9
2.0
2.5[/tt]

And you were asked to create a logic statement that selected all the numbers which started with 1. It would look something like:

[tt]X >= 1.0 AND X < 2[/tt]

So similarly, when you want to select everything from 1/15/2004, it needs to look like this:

Select  *
  From  TableA
 Where  MYDATE >= '2004-01-15'
   And  MYDATE < '2004-01-1[red]6[/red]'

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[cLFlaVA]

I'll stop talking now.


*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[nomi2000]

Thanks Esquared
you are right but i can't do the query you told me i.e
Select *
From TableA
Where MYDATE >= '2004-01-15'
And MYDATE < '2004-01-16'
beacuse user can select filter out for a particular day say 15th of January and in this case i have to add one day at front-end before passing it to back-end
so i tried and now working for me
Select @Sql='Select * from TableA
Where MYDATE between '''+ Convert(varchar,MYDATE ,101) + ' 00:00:00'' and ''' + Convert(varchar,MYDATE ,101) +' 23:59:59'''

Exec(sql)

Thanks again
Nouman

Nouman Zaheer
Software Engineer
MSR

 

[ESquared]

Wait wait wait!

What data type is the MYDATE field?

How are you passing in the requested date from the front end to the back end? If it's with a variable, what is the data type?

Are you using a stored procedure or creating the query in the front-end, or what?

There is absolutely no need to use SQL here.


There are still some problems with the query you have given, which will give you the wrong results if they are not corrected.

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[ESquared]

Sorry, that was supposed to say

"There is absolutely no need to use dynamic SQL here."

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[nomi2000]

HI
MYDATE is passed as DATE Type from front-end.
I am passing it as a parameter to SQL Server Stored Procedure as Datetime variable in the SP.
You said
"There is absolutely no need to use dynamic SQL here."
How it is possible for the filtering of data for a specific date i have to add 00:00:00 and 23:59:59 to the date passed from the front-end as it will not have this part.?

"There are still some problems with the query you have given, which will give you the wrong results if they are not corrected."
What problems?


I am using VB.NEt as fron-end and SQL server 2000 as back-end

Thanks
Nouman

Nouman Zaheer
Software Engineer
MSR

 

[ESquared]

Wait, you are confusing the MYDATE field from the database with the value passed from the front-end.

This can't possibly be working properly for you:

Select @Sql='Select * from TableA
  Where MYDATE between '''+ Convert(varchar,MYDATE ,101) + ' 00:00:00'' and ''' +  Convert(varchar,MYDATE ,101) +' 23:59:59'''

As I see it, MYDATE in all three places references the same thing so you'll end up with all records, regardless of the date.

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[nomi2000]

Sorry Esquared in haste i put the wrong thing in my last sql acually it should be
Select @Sql='Select * from TableA
Where MYDATE between '''+ Convert(varchar,@P_StartDate ,101) + ' 00:00:00'' and ''' + Convert(varchar,@P_EndDate ,101) +' 23:59:59'''

Where @P_StartDate ,@P_EndDate are Stored procedure's parameters passed from front-end
Is there anyother way for what i want to accomplish for the problem regarding filtering data for a particular date?
Thanks



Nouman Zaheer
Software Engineer
MSR