Convert Function

Question

Post reply

Convert Function

vani_r14

SSCrazy

Points: 2843
More actions
April 16, 2010 at 1:43 am

#219293

HI all
I have a doubt with the convert function and would like some clarification please...
I have table called service with two columns serviceno and sdate.
Sample data
Serviceno sdate
12345 2010-01-15 00:00:000
12478 2010-01-16 00:00:000
and so on
I ran the following query
select serviceno, convert(varchar(10), sdate, 103)
from service
where convert(varchar(10), sdate, 103) >= '14/04/2010'
and convert(varchar(10), sdate, 103) <= '16/04/2010'
when executed i got results with all serviceno with the dates starting on the 14th, 15th and 16th but the months and years were from 2005, 2006, 2007,2008,2009,2010
sample results
serviceno sdate
10000 14/01/2010
10001 14/01/2009
10002 15/01/2010
.....
so the first part of the date i.e. the day part was between 14 - 16 but the rest was not..
Can some please suggest some ideas or may be why this is happening
Thanks
Vani
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

yes, you are converting the date to a varchar and then comparing that - which will just be an alphanumeric comparison.

You need to convert your reference date, which are currently varchars to datetime types and then compare....

select serviceno, convert(varchar(10), sdate, 103)

from service

where sdate >= convert(datetime,'20100414')

and sdate < convert(datetime, '20100417')

Notice that this will also help the optimiser as it does not have to convert the data in the table and so can use an index on sdate, also note that I changed the dates to a non-region sensitive format and moved the second one forward a day but used a "less than" comparison.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

vaibhav.tiwari SSCommitted Points: 1976 More actions · Answer 2

mister.magoo (4/16/2010)
yes, you are converting the date to a varchar and then comparing that - which will just be an alphanumeric comparison.
You need to convert your reference date, which are currently varchars to datetime types and then compare....
select serviceno, convert(varchar(10), sdate, 103)
from service
where sdate >= convert(datetime,'20100414')
and sdate < convert(datetime, '20100417')
Notice that this will also help the optimiser as it does not have to convert the data in the table and so can use an index on sdate, also note that I changed the dates to a non-region sensitive format and moved the second one forward a day but used a "less than" comparison.

Or this

select serviceno, convert(varchar(10), sdate, 103)

from service

where sdate BETWEEN CONVERT( DATETIME, '12/04/2010', 103) AND CONVERT( DATETIME, '16/04/2010', 103)

Note that u can use Between instead of >= and <=

is easier and readable

Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

vaibhav.tiwari (4/17/2010)
mister.magoo (4/16/2010)
yes, you are converting the date to a varchar and then comparing that - which will just be an alphanumeric comparison.
You need to convert your reference date, which are currently varchars to datetime types and then compare....
select serviceno, convert(varchar(10), sdate, 103)
from service
where sdate >= convert(datetime,'20100414')
and sdate < convert(datetime, '20100417')
Notice that this will also help the optimiser as it does not have to convert the data in the table and so can use an index on sdate, also note that I changed the dates to a non-region sensitive format and moved the second one forward a day but used a "less than" comparison.
Or this
select serviceno, convert(varchar(10), sdate, 103)
from service
where sdate BETWEEN CONVERT( DATETIME, '12/04/2010', 103) AND CONVERT( DATETIME, '16/04/2010', 103)
Note that u can use Between instead of >= and <=
is easier and readable

Except if sdate includes a time, in which case dates such as '16/04/2010 12:23:21' will not be returned.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

vaibhav.tiwari SSCommitted Points: 1976 More actions · Answer 4

Except if sdate includes a time, in which case dates such as '16/04/2010 12:23:21' will not be returned

I am not getting please explain...

Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 5

vaibhav.tiwari (4/17/2010)
Except if sdate includes a time, in which case dates such as '16/04/2010 12:23:21' will not be returned
I am not getting please explain...

Sure... it boils down to that you should never assume that you will have whole dates (dates with no times).

Let's "parameterize" your code to make it just a little easier to explain...

DECLARE @StartDate DATETIME,

@EndDate DATETIME

SELECT @StartDate = '12/04/2010',

@EndDate = '16/04/2010'

SELECT serviceno, CONVERT(VARCHAR(10), sdate, 103)

FROM dbo.service

WHERE sdate BETWEEN @StartDate AND @EndDate

In the above, if you want to include all of 16/04/2010, then the code above won't do it because you have a whole date. Any sdate's on 16/04/2010 that also have times will be missed because things like '16/04/2010 12:23:21' are beyond the range of the WHERE clause. The WHERE clause above will only include things up to and including '16/04/2010 00:00:00'.

In order to fix that particular problem (forever), your code should look like the following...

DECLARE @StartDate DATETIME,

@EndDate DATETIME

SELECT @StartDate = '12/04/2010',

@EndDate = '16/04/2010'

SELECT serviceno, CONVERT(VARCHAR(10), sdate, 103)

FROM dbo.service

WHERE sdate >= @StartDate

AND sdate < DATEADD(dd,1,@EndDate)

Since "sdate < DATEADD(dd,1,@EndDate)

" will be interpreted as "sdate < '17/04/2010'", you'll be sure to include all times for '16/04/2010' without including any rows that have a '17/04/2010' date. Also, you should never assume that you'll always have dates with no times unless, maybe, you're using the new DATE datatype in 2K8.

Your code also has another bit of a problem especially when it comes to large batches. DateTime conversions to VARCHAR use twice as much CPU time and twice as much duration. With that in mind, I'm hoping that sdate is actually a DateTime datatype (it really should be for more reasons than I care to post here... you could write a whole article on the mistake of storing character based dates) especially if you're trying to do anything with a large number of rows... and you should always write code to not be adversely affected if the number of rows ever increase (again, you could write a whole article on why you should always write scalable code even if you "know" the number of rows will never increase).

So, the bottom line for date ranges in WHERE clauses is...

1. Never use BETWEEN.

2. Never use character based conversions.

3. Never use any type of formula on the columns that contain dates because they're not sargeable and cannot be made to use an index properly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mister.magoo SSC-Forever Points: 47174 More actions · Answer 6

vaibhav.tiwari (4/17/2010)
Except if sdate includes a time, in which case dates such as '16/04/2010 12:23:21' will not be returned
I am not getting please explain...

What Jeff said....

(I was just about to post my reply when yours popped up Jeff - and it covers everything I was going to say...)

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 7

mister.magoo (4/17/2010)
(I was just about to post my reply when yours popped up Jeff - and it covers everything I was going to say...)

Heh... I finally beat someone to the punch for a change. 🙂 You and several other frequent posters have been doing an awesome job... so awesome , in fact, I was getting worried that there wouldn't be enough unanswered T-SQL questions for me to finally hit 20k. :hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

WayneS SSC Guru Points: 95461 More actions · Answer 8

Jeff Moden (4/17/2010)
mister.magoo (4/17/2010)
(I was just about to post my reply when yours popped up Jeff - and it covers everything I was going to say...)
Heh... I finally beat someone to the punch for a change. 🙂 You and several other frequent posters have been doing an awesome job... so awesome , in fact, I was getting worried that there wouldn't be enough unanswered T-SQL questions for me to finally hit 20k. :hehe:

mister.magoo... do you think he's figured out that we've been trying to keep him under 20k?:w00t:

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2