SubQuery

Question

Post reply

SubQuery

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

Yes. 103 is part of the Convert Datetime function:

SELECT *, CONVERT( varchar, CONVERT( datetime, Date_Lst_Iss,103))

FROM dbo.Part_Mast

WHERE Part_Num LIKE '258095GB%'

ORDER BY Part_Num

I wasn't born stupid - I had to study.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 2

Sorry Farrell, but Carl is right.

The 103 style operator belongs to the outer convert, not the inner. The inner converts a char/varchar to datetime, and the outer converts that datetime back to varchar with the 103 display format. The display operators can only be used on char/varchar datatypes, not on datetimes.

/Kenneth

sada-268493 Old Hand Points: 309 More actions · Answer 3

Thank you Carl!

select *

, convert(varchar,convert(datetime,Date_Lst_Iss),103) as UKdate

from dbo.Part_Mast

where Part_Num LIKE (RTRIM('258095GB') + '%')

order by Part_Num

This query actually provides me with the correct results i was looking for. Thank you very much. I am new to sql, I did not know you could place a comma after the * thank you very much, the sql you have provided is also short and simple making it easy to put it in all my if and else statments. Thanx

sada-268493 Old Hand Points: 309 More actions · Answer 4

The query I asked about was fairly simple and i didnt think details of the table would be necessary because I needed all the information from the table and looking at the query i thougth it was obvious, the problem was that i was unable to combine the date and orginal query. Therefore I was trying to enter the date as a sub-query but it was just not excepting it. Thanx to Carl i dont need to use a sub-query.

Farrell Keough SSCoach Points: 17414 More actions · Answer 5

Doh! A least I was consistently knuckleheaded....

Thanks folks!

I wasn't born stupid - I had to study.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 6

Well, glad your problem is solved.

Just a final word about 'select *'.. Carl (hopefully) used the '*' for demonstration purposes only. You, as coder, should never write any queries that uses 'select *' - you should use 'select col1, col2.... etc on code that is to be placed into production.

It is considered bad (read 'lazy') programming habits to use 'select * from ...' constructs.

/Kenneth

sada-268493 Old Hand Points: 309 More actions · Answer 7

That is very true but in this case I had to use * to collect all the fields from the database and insert them into crystal report. The fields used are changed frequently in the Crystal report, therefore in this case particularly I had no choice but to use the *.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

What, they can edit the report but they can't run and alter proc???

sada-268493 Old Hand Points: 309 More actions · Answer 9

They can't alter the stored procedure but they can request changes to the report.

Farrell Keough SSCoach Points: 17414 More actions · Answer 10

I gotta go with Remi and Kenneth on this one! We use Crystal too, (it is very picky).

This is a process problem and probably something you cannot directly effect - at least not without a lot meetings and documentation, etc. Too bad... But at least you know the correct way and they solved your problem...

I wasn't born stupid - I had to study.