SQL how to input a date
As with any other date/time format used by SQL Server, the time or date itself is always optional. If the time portion is not specified, SQL Server will use midnight as the default; if the date portion is not specified, SQL Server will use January 1st 1900 as the date. Remember that SQL Server does not store the input date string; the date is converted into a binary format. So if invalid dates do end up in the database, there will be no way of reconstituting them from just the data. Another way to tell SQL Server how to interpret a string as a date is to use the SET DATEFORMAT command. This command allows you to input strings that would normally not be recognized by SQL server as dates. The SET DATEFORMAT command lets you specify what part of the date is first, second or third. I want to enter 30/10/1988 as the date to a DOB column in a table using a procedure. ...but it gives an error when i enter like this '30/10/1988'. Msg 8114, Level 16, State 5, Procedure addCustomer, Line 0 Error converting data type varchar to datetime. Inputting the date in PL/SQL is same as in SQL. Simply pass the date as a string in the format DD-MON-YYYY. Eg: SELECT * FROM TABLE WHERE HIRE_DATE='dd-mon-yyyy'. Or. You can also use Date functions TO_DATE('25-aug-2008','......date formate......'); Hope this is the answer, you are looking for. A good example of a date format that is not recognized by SQL Server is an 8 digit date in MMDDYYYY format, this 8 character date format is not recognized by SQL Server as a date. In order to get the MMDDYYYY date into SQL Server you will need to convert it to a format that SQL Server knows is a date, like 'MM-DD-YYYY'. Up to this point I have only been describing how to input the date portion of a date/time variable, there is still the time portion to consider. It is highly likely that you will be working with data that needs to be stored in SQL Server that has a time portion. In order to get the MMDDYYYY date into SQL Server you will need to convert it to a format that SQL Server knows is a date, like 'MM-DD-YYYY'. Let's look at ways to convert date/time data into a format that SQL Server can recognize. SQL Server comes with the following data types for storing a date or a date/time value in the database: 1  DATE-format YYYY-MM-DD. 2  DATETIME-format: YYYY-MM-DD HH:MI:SS. 3  SMALLDATETIME-format: YYYY-MM-DD HH:MI:SS. You will need to have a datetime column in a table. Then you can do an insert like the following to insert the current date: If it is not today's date then you should be able to use a string and specify the date format : You do not always need to convert the string either, often you can just do something like:
Use the SET DATEFORMAT command. This command allows you to input strings that would normally not be recognized by SQL server as dates. The SET DATEFORMAT command lets you specify what part of the date is first, second or third.