Creating a custom data type with specific format

Question

I'm moving from an access database to a SQL Server database and wanted to know if I can create my own data type for dates.

I want to have my custom date type to have a format of mm/dd/yyyy instead of yyyy/mm/dd. Is this possible to do?

Internally a date is saved always the same. But you can change the representation of a date when you select it — juergen d
– juergen d, Commented Jun 28, 2015 at 0:53
So what if I input data in a mm/dd/yyyy format? Will it automatically convert it to the date data type format? — Diogenes
– Diogenes, Commented Jun 28, 2015 at 1:10
From client app. (example: C#, VB.NET) I would send these values to SQL Server as DateTime parameters not as string. — Bogdan Sahlean
– Bogdan Sahlean, Commented Jun 28, 2015 at 3:32

M.Ali · Accepted Answer · 2015-06-28 01:31:45Z

1

Use the default/built-in data type to store a specific data type. This will be more efficient and reliable as sql server will only allow the valid data to be stored (data integrity).

Also this will allow you to make use of all the built-in functions to work with that specific data type. In your case if you use Sql Server's datetime data type you will be able to make use of all the datetime functions (DATEDIFF() , DATEADD() , DAY() , YEAR() , MONTH(), DATENAME() etc).

As far as how you see the date/datetime values stored in your database, again you will always have built-in datatime functions to format the date values as it suits you.

Once you have stored the date/datetime values in sql server database and you want to view date value as mm/dd/yyyy instead of yyyy/mm/dd simply do the following:

SELECT CONVERT(VARCHAR(10), [DateColumn], 101)

Custom datatypes are there to be used but there are some very odd issues with them, avoid them whenever you can :)

answered Jun 28, 2015 at 1:31

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Diogenes Over a year ago

Ahh okay, it would just be simpler to have change the way my users will input data. I've going to connect this db to a website so people can input data and what not. I'll just have to have drop down boxes vs a text box.

Diogenes Over a year ago

I forgot to ask--What is the 101 for?

Zohar Peled Over a year ago

Google cast and convert and see the msdn page. It basically a numeric code to specify the format.

M.Ali Over a year ago

I don't know what language you are using for application code, I know in C# for date/date time values we have DateTimePicker (on the front end it is shown as a calendar to pick dates from) this allow users to pick only valid date values.

M.Ali Over a year ago

101 is a date format style, when you use convert() function it has a third optional parameter i.e format style, there are many styles available by Microsoft again only if you have data in SQL server's datetime data type you can use this third optional parameter to format your dates when viewing data

Collectives™ on Stack Overflow

Creating a custom data type with specific format

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related