In SQL Server, for the data type datetime, can I insert like "00-00-0000" from the front end? It is giving an error. I want to insert "00-00-0000" if the user did not enter anything.
Add a comment
|
1 Answer
00-00-0000 is not a date. If the user didn't enter anything, and that's ok, enter NULL. If you really want to show it as 00-00-0000 on the client side, you can do so using COALESCE, e.g.
DECLARE @d TABLE(d DATETIME);
INSERT @d SELECT '2011-01-01'
UNION ALL SELECT NULL;
SELECT COALESCE(
CONVERT(CHAR(10), d, 120), '0000-00-00')
FROM @d;
4 Comments
Surya sasidhar
Aaron thanks for reply, is it not possible from frent end, i am using asp.net.
Aaron Bertrand
I'm not sure what you mean by "not possible" - asp.net knows whether the user entered a date or not, doesn't it? And so can pass the keyword
NULL instead of a formatted string?Surya sasidhar
Aaron actually, when user not enter date option then null will taken, but it is giving error, saying format is not correct, becuase i am converting the value which is in textbox into datetime.
Aaron Bertrand
You need to pass
NULL with no quotes. You are never going to be able to store non-date strings like '00-00-0000' or 'NULL' or 'be-fo-fart' in a datetime column.