Prev: set ARITHABORT on
Next: Arithabort
From: SQL Programmer on 18 Apr 2010 12:34
Hello:

I get the following error message in SQL 2005 Management Studio:
Conversion failed when converting datetime from character string.

I get this when I run a "select * from" query against a view called
"DEBITMEMOGLFISCALYEARS". Here is the programming of this view:

ALTER VIEW [dbo].[DEBITMEMOGLFISCALYEARS] AS
SELECT CASE WHEN GLPOSTDT BETWEEN '2007-03-01 00:00:00.000' AND '2008-02-29
00:00:00.000'
THEN '2007' WHEN GLPOSTDT BETWEEN '2008-03-01 00:00:00.000' AND '2009-02-28
00:00:00.000' THEN '2008' WHEN
GLPOSTDT BETWEEN '2009-03-01 00:00:00.000' AND '2010-02-28 00:00:00.000'
THEN '2009'
WHEN GLPOSTDT BETWEEN '2010-03-01 00:00:00.000' AND '2011-02-28
00:00:00.000' THEN '2010'
END AS GLFISCALYEAR, GLPOSTDT
FROM dbo.DEBITMEMOFISCALYEARS

As you can see, this view references another view called
"DEBITMEMOFISCALYEARS". Here is the programming for it:

ALTER VIEW [dbo].[DEBITMEMOFISCALYEARS] as
SELECT DATEPART(year, GLPOSTDT) AS YEAR, GLPOSTDT
FROM dbo.RM20101
WHERE RMDTYPAL = '3'

What syntax do I need to place in the first view (DEBITMEMOGLFISCALYEARS) in
order to not have this message appear?

Thanks!

SQL Programmer (it's just a name)
From: SQL Programmer on 18 Apr 2010 12:52
Disregard....I figured this out. The actual query had spaces between the
date and time. Once i eliminated those spaces, all was well

SQL Programmer (it's just a name)

"SQL Programmer" wrote:

> Hello:
>
> I get the following error message in SQL 2005 Management Studio:
> Conversion failed when converting datetime from character string.
>
> I get this when I run a "select * from" query against a view called
> "DEBITMEMOGLFISCALYEARS". Here is the programming of this view:
>
> ALTER VIEW [dbo].[DEBITMEMOGLFISCALYEARS] AS
> SELECT CASE WHEN GLPOSTDT BETWEEN '2007-03-01 00:00:00.000' AND '2008-02-29
> 00:00:00.000'
> THEN '2007' WHEN GLPOSTDT BETWEEN '2008-03-01 00:00:00.000' AND '2009-02-28
> 00:00:00.000' THEN '2008' WHEN
> GLPOSTDT BETWEEN '2009-03-01 00:00:00.000' AND '2010-02-28 00:00:00.000'
> THEN '2009'
> WHEN GLPOSTDT BETWEEN '2010-03-01 00:00:00.000' AND '2011-02-28
> 00:00:00.000' THEN '2010'
> END AS GLFISCALYEAR, GLPOSTDT
> FROM dbo.DEBITMEMOFISCALYEARS
>
> As you can see, this view references another view called
> "DEBITMEMOFISCALYEARS". Here is the programming for it:
>
> ALTER VIEW [dbo].[DEBITMEMOFISCALYEARS] as
> SELECT DATEPART(year, GLPOSTDT) AS YEAR, GLPOSTDT
> FROM dbo.RM20101
> WHERE RMDTYPAL = '3'
>
> What syntax do I need to place in the first view (DEBITMEMOGLFISCALYEARS) in
> order to not have this message appear?
>
> Thanks!
>
> SQL Programmer (it's just a name)
From: Mark McGinty on 18 Apr 2010 22:38

"SQL Programmer" <SQLProgrammer(a)discussions.microsoft.com> wrote in message
news:899459A9-B3EC-4558-BC3A-AE55C40DE6C4(a)microsoft.com...
> Disregard....I figured this out. The actual query had spaces between the
> date and time. Once i eliminated those spaces, all was well

Couldn't GLFISCALYEAR be derived like this?

DATEPART(yyyy, DATEADD(mm, -2, GLPOSTDT))

That should return the current year on and after March 1st, and the previous
year up to and including the lasy day of Februrary (leap year or not.)


-MM



> SQL Programmer (it's just a name)
>
> "SQL Programmer" wrote:
>
>> Hello:
>>
>> I get the following error message in SQL 2005 Management Studio:
>> Conversion failed when converting datetime from character string.
>>
>> I get this when I run a "select * from" query against a view called
>> "DEBITMEMOGLFISCALYEARS". Here is the programming of this view:
>>
>> ALTER VIEW [dbo].[DEBITMEMOGLFISCALYEARS] AS
>> SELECT CASE WHEN GLPOSTDT BETWEEN '2007-03-01 00:00:00.000' AND
>> '2008-02-29
>> 00:00:00.000'
>> THEN '2007' WHEN GLPOSTDT BETWEEN '2008-03-01 00:00:00.000' AND
>> '2009-02-28
>> 00:00:00.000' THEN '2008' WHEN
>> GLPOSTDT BETWEEN '2009-03-01 00:00:00.000' AND '2010-02-28 00:00:00.000'
>> THEN '2009'
>> WHEN GLPOSTDT BETWEEN '2010-03-01 00:00:00.000' AND '2011-02-28
>> 00:00:00.000' THEN '2010'
>> END AS GLFISCALYEAR, GLPOSTDT
>> FROM dbo.DEBITMEMOFISCALYEARS
>>
>> As you can see, this view references another view called
>> "DEBITMEMOFISCALYEARS". Here is the programming for it:
>>
>> ALTER VIEW [dbo].[DEBITMEMOFISCALYEARS] as
>> SELECT DATEPART(year, GLPOSTDT) AS YEAR, GLPOSTDT
>> FROM dbo.RM20101
>> WHERE RMDTYPAL = '3'
>>
>> What syntax do I need to place in the first view (DEBITMEMOGLFISCALYEARS)
>> in
>> order to not have this message appear?
>>
>> Thanks!
>>
>> SQL Programmer (it's just a name)


 | 
Pages: 1
Prev: set ARITHABORT on
Next: Arithabort