• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 460
  • Last Modified:

convert function of SQL Server 2000 giving wrong results.

Hi,

I am using SQL Server 2000 and I have a table named BTH_INFO with BTH_DT datetime field and in this field I am having data with both date and time together, but when I am executing the following query

"select * from BTH_INFO where convert(nvarchar,BTH_DT,101) >= '03/30/2009' and convert(nvarchar,BTH_DT,101) <= '03/31/2009' order by ltrim(rtrim(BTH_ID))"
I am getting rows with BTH_DT 03/31/2008, 03/30/2009 and 03/31/2009.

And when I am executing the following query:

select * from BTH_INFO where convert(nvarchar,BTH_DT,101) >= '03/30/09' and convert(nvarchar,BTH_DT,101) <= '03/31/09' order by ltrim(rtrim(BTH_ID))
I am getting rows with BTH_DT 03/30/2009 only.

Please give me a solution to the above problem so that I will get the correct result.

Regards,
Pranjal
0
pranjal_ds
Asked:
pranjal_ds
  • 3
  • 2
1 Solution
 
pivarCommented:
Hi,

That's because converttype 101 gives you the year with 4 digits.
So if BYH_DT is '03/31/2008' your WHERE looks like this:

select * from BTH_INFO where  '03/31/2008' >= '03/30/09' and  '03/31/2008' <= '03/31/09' order by ltrim(rtrim(BTH_ID))

If you only wants 2 digits in the year use converttype 1
convert(nvarchar,BTH_DT,1)

/peter
0
 
pranjal_dsAuthor Commented:
Hi,

Actually the database was in SQL Server 2000 and now I am accessing it in Microsoft SQL Server 2005 Management Studio Express. Will that cause such errors?
I used convert(nvarchar,BTH_DT,1) still it is showing me  rows with BTH_DT 03/31/08, 03/30/09 and 03/31/09, when actually I should get rows of 03/31/09 & 03/30/09 only.

Please advice and give solution.

-Pranjal
0
 
pivarCommented:
No, that shouldn't matter.

I'm sorry of course it does, it's string comparison and the string '03/31/08' is bigger than '03/30/09'.

Try this, or can change the dateformat on your datecriteria?
set dateformat mdy

select *
  from BTH_INFO
    where BTH_DT BETWEEN convert(datetime,'03/30/09') and convert(datetime,'03/31/09')
order by ltrim(rtrim(BTH_ID))

If you want to keep the string comparison the date must be in YMD format.

By the way if you have an index on BHT_DT it will not be used if you have convert in the where clause.
0
 
pivarCommented:
My last post got somewhat incomprehensible.

First. It doesn't matter if you are using 2005 or 2000.

Then the line
"Try this, or can change the dateformat on your datecriteria?"

What I meant was,
Try this select. But I think it's better if it's possible for you to change the datecriteria to standard dateformat '20090330'
0
 
pranjal_dsAuthor Commented:
Thank you for guiding me. I was able to get the correct result by including the end time 23:59:00 in To date in the select query.

Regards,
Pranjal
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now