SELECT dbo.Consultation.ConsultationID, dbo.LkupConsultationType.ConsultationTypeID, CONVERT(VARCHAR(10), dbo.Consultation.ConsultationDate, 103) AS ConsultationDate
FROM dbo.Consultation WITH (tablock) LEFT OUTER JOIN
dbo.LkupConsultationType ON ISNULL(dbo.Consultation.ConsultationType, 0) = ISNULL(dbo.LkupConsultationType.ConsultationType, 0)
The problem is that it converts 'ConsultationDate' to varchar(10).
On the table dbo.Consultation.ConsultationDate is datatype "Datetime".
The Results is fine and ConsultationDate is in the expected format 26/02/2006, which is OK. But the statement makes the column varchar(10) and I want it to retain the Date datatype.
DATE (and other Date and Time) data types are not stored in SQL Server as character strings. So the information SQL Server holds just indicates that the date is 26th of February, 2006 and does not include any formatting information such as whether to use DD/MM/YYYY format or whether to use two-digit or 4-digit year etc.
So if you want it formatted a specific way, it is going to be varchar(10) (or some other character data type). It cannot be DATE data type.
The recommended practice is to send the data as DATE data type to the end-user (such as the GUI display or reporting services) and have the end-user format it as required.
If you just want to remove the time portion, then instead of CONVERT(VARCHAR(10), dbo.Consultation.ConsultationDate, 103), use "CAST(dbo.Consultation.ConsultationDate AS DATE)"
SELECT
Consultation.ConsultationID,
LkupConsultationType.ConsultationTypeID,
CONVERT(VARCHAR(10), Consultation.ConsultationDate, 103) AS ConsultationDate
FROM dbo.Consultation Consultration WITH (tablock)
LEFT OUTER JOIN dbo.LkupConsultationType LkupConsultationType ON ISNULL(Consultation.ConsultationType, 0)
This appears to be a bug as described in this connect issue. See if any of the workarounds listed there will fix your problem?
You don't necessarily have to use the designer. You can simply write the query in a SSMS query window if you have some familiarity with the structure of SQL select statements, and know the columns/where conditions etc. that you want to include.
Maybe you can use CONVERT instead of CAST to output a date rather than a datetime.
You should never use ISNULL() in a JOIN (because it can cause performance issues). Can there be a ConsultationType of 0 in the table or is that only being used as a substitute for NULL?
Btw, I used table aliases to make the code (much) more readable.
SELECT c.ConsultationID, lct.ConsultationTypeID, CONVERT(date, c.ConsultationDate) AS ConsultationDate
FROM dbo.Consultation c WITH (tablock) LEFT OUTER JOIN
dbo.LkupConsultationType lct ON ISNULL(c.ConsultationType, 0) = ISNULL(lct.ConsultationType, 0)