SQL Server function to return minimum date January 1 1753

Question

I am looking for a SQL Server function to return the minimum value for datetime  namely January 1  1753  I d rather not hardcode that date value into my script   Does anything like that exist   For comparison  in C   I could just do DateTime MinValue  Or would I have to write this myself   I am using Microsoft SQL Server 2008 Express

User · Answer

Have you seen the SqlDateTime object  use SqlDateTime MinValue to get your minimum date  Jan 1 1753

User · Answer

Enter the date as a native value  yyyymmdd  to avoid regional issues   select cast  17530101  as datetime    Yes  it would be great if TSQL had MinDate      00010101   but no such luck

User · Answer

The range for datetime will not change  as that would break backward compatibility   So you can hard code it

User · Answer

This is what I use to get the minimum date in SQL Server  Please note that it is globalisation friendly   CREATE FUNCTION  dbo   DateTimeMinValue    RETURNS datetime AS BEGIN   RETURN  SELECT     CAST  17530101  AS datetime   END   Call using   SELECT  dbo   DateTimeMinValue

User · Answer

You could write a User Defined Function that returns the min date value like this   select cast -53690 as datetime    Then use that function in your scripts  and if you ever need to change it  there is only one place to do that   Alternately  you could use this query if you prefer it for better readability   select cast  1753-1-1  as datetime    Example Function  create function dbo DateTimeMinValue   returns datetime as begin     return  select cast -53690 as datetime   end   Usage  select dbo DateTimeMinValue   as DateTimeMinValue  DateTimeMinValue ----------------------- 1753-01-01 00 00 00 000

User · Answer

As I can not comment on the accepted answer due to insufficeint reputation points my comment comes as a reply   using the select cast  1753-1-1  as datetime  is due to fail if run on a database with regional settings not accepting a datestring of YYYY-MM-DD format   Instead use the select cast -53690 as datetime  or a Convert with specified datetime format

User · Answer

Here is a fast and highly readable way to get the min date value  Note  This is a Deterministic Function  so to improve performance further we might as well apply WITH SCHEMABINDING to the return value   Create a function  CREATE FUNCTION MinDate   RETURNS DATETIME WITH SCHEMABINDING AS BEGIN     RETURN CONVERT DATETIME  -53690   END   Call the function  dbo MinDate     Example 1  PRINT dbo MinDate     Example 2  PRINT  The minimimum date allowed in an SQL database is     CONVERT VARCHAR MAX   dbo MinDate      Example 3  SELECT   FROM Table WHERE DateValue  gt  dbo MinDate     Example 4   SELECT dbo MinDate   AS MinDate   Example 5  DECLARE  MinDate AS DATETIME   dbo MinDate    SELECT  MinDate AS MinDate

User · Answer

It s not January 1  1753 but select cast    as datetime  wich reveals  1900-01-01 00 00 00 000 gives the default value by SQL server    Looks more uninitialized to me anyway

[sql-server] SQL Server function to return minimum date (January 1, 1753)

Examples related to sql-server

Examples related to datetime

Examples related to function