How to store a date field in format mm/dd/yyyy into a SQL Server 2008 table?

I am using SQL Server 2008.

If I pass a date value in format "mm/dd/yyyy" as a parameter into a date field titled DateInput within a table named tblDate, is there a way to store this value in this format into this SQL Server table?
zimmer9Asked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
>is there a way to store this value in this format into this SQL Server table?
No.  Dates are stored as a double number, and not in any specific cosmetic format such as mm/dd/yyyy.

I also wouldn't recommend storing dates as char/varchar's as well, as doing so would reflect character sorting and not date sorting, and you would still have to convert it back to a date format in order to do date specific functions such as DATEADD, DATEPART, DATEDIFF.

>table named tblDate
Tell us more about this tblDate.  I have my own tblDate article you can read called SQL Server Calendar Table, and what you're looking for may be in there.
0
 
Russ SuterCommented:
If you store the value in a column of data type string then yes. If you store it as a Date, DateTime, or DateTime2 data type then no. However you can format the data on the way out. Your SELECT query would look something like this:
SELECT
  CONVERT(VARCHAR, DateInput, 103) as [DateInput]
FROM
  tblDate

Open in new window

0
 
Russ SuterConnect With a Mentor Commented:
Be warned, using a CONVERT statement in a SQL query can reduce performance. This is not something you should normally be doing in SQL. Conversions like this should be in the business logic layer of your application. However, if you need it then the SQL query provided will give you a formatted string as you desire.
0
 
zimmer9Author Commented:
I think you meant 101 but I understand. Thanks.  :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.