Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 65
  • Last Modified:

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?
0
zimmer9
Asked:
zimmer9
  • 2
2 Solutions
 
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
 
Jim HornMicrosoft 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:
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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now