CODEWITHSUNDEEP
sqlsql-serverdatabase
bajji
bajji

Reputation: 1291

SQL Table datetime column format

I tried googling to get an answer but in vain. Below is my requirement

  1. User has an option to insert data into a table which has export_date as datetime
  2. When they execute insert statements, I want to ensure that they have keyed in date in "dd-MM-yyyy hh:mm:ss" format. If not, don't allow insert queries to run.
  3. Or allow the user to enter date in any format like dd-MM-yyyy or dd/MM/yyyy but internally convert it into the required format "dd-MM-yyyy hh:mm:ss" and store

Can someone help/guide me?

Upvotes: 1

Views: 3320

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81930

You can use Set DateFormat

Example

Used data type of date for illustration, but clearly you can use datetime

Set DateFormat DMY
Select try_convert(date,'15/08/2017')   -- Returns 2017-08-15

Set DateFormat MDY
Select try_convert(date,'15/08/2017')   -- Returns NULL

Set DateFormat YMD
Select try_convert(date,'15/08/2017')   -- Returns NULL

Upvotes: 3

ikaikastine
ikaikastine

Reputation: 629

You will likely run into issues if you want the user to input the date in the "dd-MM-YYYY" format since if the user inputs in the mm-dd-yyyy format, you'll get different results. "YYYMMDD" is a generic format that SQL Server will always interpret properly.

Once you get the date from the user, you can convert it using the particular format that you want. The following will convert the date to the ISO8601 format: 

SELECT   
    GETDATE() AS UnconvertedDateTime,   
    CONVERT(nvarchar(30), GETDATE(), 126) AS UsingConvertTo_ISO8601  ;  
GO

For more information on the specific date formats, I'd recommend checking out Microsoft's Convert Functions.

Upvotes: 1

Related Questions