SQLTeam.com | Weblogs | Forums

Select Statement - Date

#1

Hi,

I have the following Select Statement;

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.

Please help me out.

Thanks

#2

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)"

#3

Tred;

CAST(dbo.Consultation.ConsultationDate AS DATE) But it gives me an error in SQL view;

Cannot call methods on date.

IN Query window it works fine. Is there any way I cant get a result working in view instead ?
Thanks

#4

Try an alias for the table

Original code

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)
#5

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.

#6

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)