CODEWITHSUNDEEP
t-sqldatetimesql-server-2008-r2
paparazzo
paparazzo

Reputation: 45096

Return empty string for no match on DateTime

Table [docSVdate]
PK sID, fielID
value DateTime not null

If there is no row for a given PK (sID, fieldID) I need to return an empty string
This is the best I could come up with
Does anyone have something more efficient? 

  select isnull(convert(varchar,[docSVdate].[value]), '')
    from [docSVsys] as [pholder] with (nolock) 
    left join [docSVdate] 
      on [docSVdate].[sID] = [pholder].[sID]
     and [docSVdate].[fieldID] = 117
   where [pholder].[sID] = 6485

Upvotes: 0

Views: 696

Answers (2)

Matt Johnson-Pint
Matt Johnson-Pint

Reputation: 241450

If you convert the datetime to a varchar in SQL, you are introducing the language and culture settings of your SQL Server into the date string. You might also be losing precision, depending on exactly what format is being used. And there's potential for error if the application code parses the string differently than SQL produced it.

So the simple answer is - don't do this. Just return it as a datetime. Let the calling application use it directly without converting to a string. Check for null if necessary in the calling application.

If you really want to do this, then there are many techniques that might work, including the join you showed, and the variable the Luis showed in his answer. Here is another technique, which uses a nested query. Perhaps this is what you are looking for:

SELECT ISNULL(
    (select convert(varchar, [value])
     from [docSVDate]
     where [sID] = 6485 and [fieldID] = 117
    ), '') as [value]

Please be aware that conversion from datetime to varchar involves formatting, whether you like it or not. If you don't specify anything, you get the defaults for whatever system you are running on. For me, running on a database with US English settings, my default format looks like "Jul 21 2013 4:41PM" But if I had different language settings, I might get a completely different result. You should probably pass a format specifier to indicate what format you are looking for. Please reference the Date and Time Styles remarks in the MSDN reference for cast/convert.

Upvotes: 2

Luis LL
Luis LL

Reputation: 2993

if it's just one row...

INNER JOIN? I'm not sure what the execution plan will show

  DECLARE @Value VARCHAR(50)
  SET @Value = ''

  SELECT @Value  = convert(varchar,[docSVdate].[value])
    FROM [docSVsys] as [pholder] with (nolock) 
    INNER JOIN [docSVdate] 
      ON [docSVdate].[sID] = [pholder].[sID]
         AND [docSVdate].[fieldID] = 117
   WHERE [pholder].[sID] = 6485

  SELECT @Value as Value

Does not need a join at all.
At most one row as the where is the PK, 

DECLARE @Value VARCHAR(50)
  SET @Value = ''

  SELECT @Value  = convert(varchar,[docSVdate].[value])
    FROM [docSVdate]         
   WHERE [docSVdate].[sID] = 6485
     AND [docSVdate].[fieldID] = 117

  SELECT @Value as Value

Upvotes: 2

Related Questions