How to use a default in SELECT query?

Question

I have created a default value like this:

create default [dbo].[MAX_DATE] as '31/12/9999 23:59:59'

Now I would like to return it from a sql query. I tried this:

SELECT 
    CASE date_field WHEN dbo.MAX_DATE THEN '' ELSE date_field END
FROM
    myTable

However, I get the following error:

Server: Msg 107, Level 16, State 2, Line 2
The column prefix 'dbo' does not match with a table name or alias name used in the query.

This might help with the retrieval of the default value: http://stackoverflow.com/questions/3817885/sql-server-find-out-default-value-of-a-column-with-a-query — Tanner, Sep 15 '14 at 09:02

score 2 · Accepted Answer · answered Sep 15 '14 at 22:22

Defaults are used by binding them to columns. The default value is applied by the server when a row is created and a column value isn't specified. (See: http://msdn.microsoft.com/en-us/library/ms173565.aspx)

Here are 3 options for you:

Option (1) It looks like you're using a "named constant" for use in doing compares. In this case, you might want to go with a function, such as:

CREATE Function [dbo].[MAX_DATE] () 
    Returns Datetime 
    as 
    BEGIN
    Return '99991231 23:59:59'
    END;
GO

select dbo.MAX_DATE()

Option (2) Another option you might consider is having a table of named constants. It might have the columns: ID (autonumber), ValueName, numValue, strValue, dtValue, binValue. You would populate the ValueName and the appropriate column depending on what type of value you're storing in it.

Option (3) To use a constant in just your current script, you can declare a value and set it's value and use it in the rest of your script. These variables are out of scope outside of their batch, so either when the script has finished running, or SQL encounters a GO statement. E.g.

DECLARE @MAX_VALUE as datetime
set @MAX_VALUE  = '99991231 23:59:59'
select @MAX_VALUE

How to use a default in SELECT query?

1 Answers1