score:0
You have to keep in mind that GETDATE() includes the current time as well. Your constraint appears to allow any value thats GreaterThanOrEqualTo the current Datetime stamp for which the value is evaluated. Which I would think in this case would be anything in the future. I'd like to see your client code that compensates for this. If your only looking to compare to the DATE then you need to change your constraint to the following.
CHECK (CONVERT(DATETIME, CONVERT(varchar(10), date_column, 101)) => CONVERT(DATETIME, CONVERT(varchar(10), GETDATE(), 101)))
Here's a quick example: SELECT GETDATE() 2011-04-26 20:39:11.240
SELECT CONVERT(DATETIME, CONVERT(varchar(10), GETDATE(), 101)) 2011-04-26 00:00:00.000
score:0
GETDATE returns the current DATE AND TIME (down to milliseconds), NOT the current DATE.
SQL Server uses a Date-Time data type. For dates only (without time information), the time is defaulted to midnight (i.e. 00:00).
If you input today's date WITHOUT A TIME, it will default to midnight (i.e. 00:00). Today's date at midnight is always going to be earlier than GETDATE (which includes the current time).
Therefore your "today" date (midnight without time specificatoin) will never be >= GETDATE. Tomorrow at midnight, though, works because it is later than GETDATE.
Experiment: Specify today's date, PLUS A TIME (say one hour later). It should be accepted.
score:2
Most practical I think would be changing the constraint to this (for the reasons others pointed out):
ALTER TABLE table1
ADD CONSTRAINT chk1 CHECK (date_column >= CAST(GETDATE() as date))
Optionally you also can change the type of your column from datetime
to date
score:3
If you are just entering today's date in the form mm/dd/yyyy that would evaluate to midnight for the current day. When you compare that to GetDate() it is evaluating to less than the current date and time You need to floor GetDate() to the start of the day.
Try using
cast(floor(cast(getdate() as float)) as datetime)
More questions
- Joining against a CSV Column in SQL Server
- How would you skip vulgar words using base 36 against an Id column in sql server 08?
- How should you construct a SQL search condition against a column of type "DateTime" using a value containing just a Date?
- SQL Server show column as delimited against a repeatable key
- SQL Validation on insert - only allow one or another column to be populated
- SQL: Column Validation against GETDATE()
- SQL Server : when querying against a XML column can I return a XML column?
- GETDATE - number of dates - a column / division in SQL
- SQL Server query for calculating time duration for column containing multiple datetime entries against order number and order state
- SQL QUERY : multiple records against one column value . need to compare another column value to fetch one record
- Possible to assign a default value on a NULL against table column in sql
- MS SQL foreign key select validation on other column
- Adding new column and set GETDATE as value in SQL Server 2017
- SQL comparing date in column against system date
- Compare the values within the same column against other columns in SQL Server
- Add a column with a default value to an existing table in SQL Server
- How to check if a column exists in a SQL Server table
- Find all tables containing column with specified name - MS SQL Server
- How can I get column names from a table in SQL Server?
- Rename column SQL Server 2008
- How to replace a string in a SQL Server Table Column
- How do you change the datatype of a column in SQL Server?
- Altering column size in SQL Server
- Best practices for SQL varchar column length
- How do I set a column value to NULL in SQL Server Management Studio?
- How to change column datatype in SQL Server database without losing data?
- How do I check if a SQL Server text column is empty?
- Can I create a named default constraint in an add column statement in SQL Server?
- Is it possible to for SQL Output clause to return a column not being inserted?
- How can I query a value in SQL Server XML column
More questions with similar tag
- Create Nuget package for Dacpac and Powershell script
- Cannot Create Database Diagram in SQL Server 2019 Database
- Unable to return stored procedure results to Excel
- T SQL Bulk Insert skipping first row with or without header
- SQL Server 2008 R2: Update table using pattern
- Limiting Query Result to number of rows of ONE of 2 JOINED tables
- Incrementing Character value in T-sql
- Dependent Parameter not Populating in Visual Studio(SSRS)
- SQL Server NULL Integer to Empty String using ISNULL
- Inserting data into table returns error code 195
- Table design
- How to implement a ZIP JOIN in T-SQL?
- SQL Server Query using GROUP BY
- I am Trying to insert this data into SQL
- MDX IN Or SubQuery
- How do I make dynamic SQL to insert 'date' variable?
- How to split a string that appears twice?
- Select date range, last 30 days
- Sql Server 2012 (T-SQL) String Comparison (for equality) gives asterisk in query results
- Is there any way to update Sql Server Agent jobs schedule time by programmatically
- Creating a SQL database without defining primary key
- Why is the smallest ID in last?
- php class _Construct empty
- Retrieve data from SQL Server with AND/OR when some parameters may be left blank
- Sql Server Drop Database failure
- Crosstab multi columns
- Do I need to rebuild my SQL server database indexes?
- MS SQL specific ordering
- New column in SQL table/view not showing in query results
- Convert date in text format to datetime format in T-SQL