Prev: How to put an "mdf" file into 2 different physical drive?
Next: Is there a way to physically re-order the records in an MS SQL 2005 table
From: soni2926 on 9 Aug 2010 13:53
Hi,
I keep getting this error:
Conversion failed when converting datetime from character string

I'm trying to get a where clause to limit the data returned to within
6 months of a certain date. I have the following:

DECLARE @LastestBiWeeklyPeriod as datetime
SET @LastestBiWeeklyPeriod = (SELECT top 1 date From BiWeeklyPeriod
ORDER BY date desc)
SELECT @LastestBiWeeklyPeriod

DECLARE @PrevSixMonthsPeriod as datetime
SET @PrevSixMonthsPeriod = (SELECT DATEADD(MM, -6,
@LastestBiWeeklyPeriod))
SELECT @PrevSixMonthsPeriod

SELECT DATEDIFF(month, @PrevSixMonthsPeriod, @LastestBiWeeklyPeriod)

these three work fine, returning:
2010-07-12 00:00:00.000
2010-01-12 00:00:00.000
6

now using those dates i tried the following:
SELECT distinct CONVERT(datetime, a.startdate, 105), startdate
FROM Client a left join SHistory b on a.ClientId = b.ClientId
WHERE DATEDIFF(month, CONVERT(datetime, a.startdate_yymmdd, 105),
@LastestBiWeeklyPeriod) < 6

but i keep getting this:
Conversion failed when converting datetime from character string.

I don't get why, as running the query without the WHERE DATEDIFF...
everything runs fine, there is no error about the same date conversion
happening in the SELECT clause. I tried checking the dates the data is
working with and they all seem fine, I didn't know any incorrect date.

the startdate is coming in like this:
20080908
20091019
20100125
20100405
20080728
so i tried to convert that to datetime to help with the between
clause. any ideas on why this is erroring out and how to get around
it?

Thank you.
From: Erland Sommarskog on 9 Aug 2010 15:10
soni2926 (soni2926(a)yahoo.com) writes:
> now using those dates i tried the following:
> SELECT distinct CONVERT(datetime, a.startdate, 105), startdate
> FROM Client a left join SHistory b on a.ClientId = b.ClientId
> WHERE DATEDIFF(month, CONVERT(datetime, a.startdate_yymmdd, 105),
> @LastestBiWeeklyPeriod) < 6
>
> but i keep getting this:
> Conversion failed when converting datetime from character string.
>
> I don't get why, as running the query without the WHERE DATEDIFF...
> everything runs fine, there is no error about the same date conversion
> happening in the SELECT clause. I tried checking the dates the data is
> working with and they all seem fine, I didn't know any incorrect date.
>
> the startdate is coming in like this:
> 20080908
> 20091019
> 20100125
> 20100405
> 20080728

You asking for startdate_yymmdd to be interpreted according to format 105,
which I don't know how it looks like on the top of my head. But that much I
know that YYYYMMDD is format 112, so 105 is likely to be incorrect.

Furthermore, YYYYMMDD is a safe format that always works, so you don't
need to specify any format code with it. Or convert either for that matter.

Overall, don't store dates in string format, but use the datetime data type
instead. This saves you from problems like this. Furthermore, it will be
possible for SQL Server to use indexes. In this case you could save the
show with writing:

a.startdate_yymmdd >
convert(char(8), dateadd(MONTH, -6, @LastestBiWeeklyPeriod), 112)

Although this is not exactly equal to what you had - then again, it
may be closer to what you want!

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: How to put an "mdf" file into 2 different physical drive?
Next: Is there a way to physically re-order the records in an MS SQL 2005 table