July 15, 2003 at 4:00 am
Hi. I'm creating a view on a table (for a report) where the date is stored in a varchar. I can take out a month, even a year and make a group by. But from the moment when i use a where statement in this view, SQL is telling me she can't convert the string into datetime. When I don't use the where it's working ??????
Anyone any idea ? Used cast, didn't work.
Kind regards, EL Jefe
JV
JV
July 15, 2003 at 4:08 am
What does your date look like? 10/10/2003?
If this is the case the SQL will fail depending on the date.
01/30/2003 might be converted and might not depending on the regional settings.
It could see 01 as jan and 30 as the day. A south african installation would see 01 as the day and 30 as.... It would die.
You can use convert(SmallDateTime, '01/03/2003', 103)
The style instructs SQL what part of it is month and what is day.
See BOL for all the convert styles. Look at CONVERT.
Cheers,
Crispin
Something as incredibly simple as
binary still gives you too many options
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
July 15, 2003 at 4:15 am
Crispin,
thx fro quick reply. Date is like April 2003 or May 2002.
Kind regards
JV
JV
July 15, 2003 at 4:36 am
When exactly does the error occurr? On the convert?
What is the error?
Can you post the view?
Does your where clause look include the convert?
Where
Convert (Datetime, 'March 2003') = '2003-03-01 00:00:00.000'
Cheers,
Crispin
Something as incredibly simple as
binary still gives you too many options
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
July 15, 2003 at 4:39 am
WHat is the exact error you get? I tried a table with a varchar(15) field and had no issues using cast on the tpyes of values you stated. Also check your user account logged in with in EM and see what the language is set to.
July 15, 2003 at 5:39 am
Crispin
Here's the view I now have tried. The LIBPER is the column that contains the date like April 2003 :
SELECT TOP 100 PERCENT UPPER(LEFT(CODCEN, 5)) AS Type_Invest, RIGHT(LIBPER, 4) AS Jaar, LEFT(LIBPER, 3) AS Maand, SUM(DEBMOI - CREMOI) AS Kost_Invest
FROM dbo.CUMANA2
WHERE (UPPER(LEFT(CODCEN, 5)) = 'IT101')
GROUP BY UPPER(LEFT(CODCEN, 5)), RIGHT(LIBPER, 4), LEFT(LIBPER, 3)
ORDER BY RIGHT(LIBPER, 4), LEFT(LIBPER, 3), UPPER(LEFT(CODCEN, 5))
The first one I've tried worked without the where clause, is this one :
SELECT TOP 100 PERCENT UPPER(LEFT(CODCEN, 5)) AS Type_Invest, year(LIBPER) AS Jaar, month(LIBPER) AS Maand, SUM(DEBMOI - CREMOI) AS Kost_Invest
FROM dbo.CUMANA2
WHERE (UPPER(LEFT(CODCEN, 5)) = 'IT101')
GROUP BY UPPER(LEFT(CODCEN, 5)), year(LIBPER), month(LIBPER)
ORDER BY year(LIBPER), month(LIBPER), UPPER(LEFT(CODCEN, 5))
Kind regards
JV
JV
July 15, 2003 at 7:04 am
I have tried both the queries as written and do not get any errors. I do not see how the where clause (present or not) would cause a date issue!!! Have you checked the actual data to see if it contains only valid data?
Edited by - davidburrows on 07/16/2003 06:36:12 AM
Far away is close at hand in the images of elsewhere.
Anon.
July 15, 2003 at 7:14 am
David
When I select * i see in that column April 2003, or May 2003, etc...
I can use year to substract the year, same for the month, except when i add a where clause. Then SQL gives an error statement cannot convert varchar into datetime.
JV
JV
July 15, 2003 at 8:40 am
I am still confused, the queries you posted already have a where clause. Are adding to the where clause, eg
AND LIBPER .....
Far away is close at hand in the images of elsewhere.
Anon.
July 15, 2003 at 10:40 am
The only reason that coverting date to char will fail is if you have "odd" values in date field. You can find this out be
Select * from table where isdate(FieldName) = 0
or to avoid selecting "odd" dates in view add
"and isdate(FieldName)" to the where clause. Hope this helps.
Thanks
Sree
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply