SQL Server date format

75 pts.
Tags:
SQL
SQL Server
SQL Server DateTime
String manipulation
Hello,
I have a problem when i am inserting a string date ie 19/9/2010,
which is stored in table as 9/19/2010.  i want to insert dd-mon-yyyy format.
can i insert dd-mon-yy format,please replay. 


Software/Hardware used:
sql server

Answer Wiki

Thanks. We'll let you know when a new response is added.

Try this

SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') AS [DD-Mon-YYYY]
The output is : 25-Dec-2005.

Now u have to put ur date here !OR
try this

SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY]

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    Do you want to insert dates in that format, or do you want to show dates in that format when querying the data you have inserted ? Where are you inserting the data from ? an application ? a stored procedure ? a script ?
    69,510 pointsBadges:
    report
  • Shamu
    I have a problem when i am inserting a string date ie 19/9/2010, I want insert the same format dd-mon-yyy.but when insert query is executed then it is inserted as mm/dd/yyyy format? why? . can i insert dd-mon-yy format,please replay.
    75 pointsBadges:
    report
  • Subhendu Sen
    most probably, this could help u.... SELECT CONVERT(VARCHAR(10), GETDATE(), ur required format) AS [DD/MM/YYYY] For better guide link here, DateFormat Tips & Tricks or u may read this ITKE-Blog
    28,600 pointsBadges:
    report
  • Kccrosser
    CarlosDL's question is still valid. I think most (all?) of us would argue that date information should be stored as a DATETIME value in the database, and then rendered in the appropriate format by conversion during retrieval. If you are actually storing a "date" in the database as "dd-mon-yyyy" format, then you are really storing that value as a string. Now, consider how you would find values in that field in some date range??? A query to find all such records with a "date" between 1-Jan-2011 and 30-Apr-2011 is ridiculously complex when you are trying to use string matching - you will find yourself having to recompute the string field into a DATETIME value for the comparison, which means you will have to do a full table scan and apply a function to the field in every row of the database to do a query. You are much better off storing the date in a DATETIME field and using CONVERT or CAST to change the displayable format on a retrieval. It is much more efficient, you can search the field on date range values, and the conversions for display formats are very simple (check SQL Server Books Online and look at the CONVERT function arguments for DATETIME values).
    3,830 pointsBadges:
    report
  • Darryn
    Hi Shamu, What you are experiencing is not actually a problem. When you insert a date into a datetime field, the database actually stores it as a number. When you look at it, 19/9/2010 = 9/19/2010, it is just being displayed differently, both show 19th September 2010, so the database is inserting it correctly. What you are seeing (i.e. 9/19/2010) is influenced by a number of factors, and can be anything from your regional settings, the software settings, or the database locale (most likely). However, you might have a problem if you are entering 06/03/2010 (dd/mm/yyyy) and the date being shown is 06/03/2010 (mm/dd/yyyy). To fix this, you will have to use the CONVERT or CAST function to insert the date correctly. You can also use the CONVERT function to display the date in the format you want, but this won't change the value of the data stored in the db. If you want to insert it in a particular format, and display it in the same format, then you will have to use the CONVERT or CAST function for both processes.
    765 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following