What is the best way to insert and retrieve value from that column. Here is the scenario
1) I want to insert value '02/03/2006' into the column. How does the sql server know that the month is 02 and not 03. Will it look into the system settings. If so then can I sepcify custom format to distinguish between month,day and year.
2) I want to retrieve value from the datetime column in the format dd/mm/yyyy hh:mm:ss AM/PM . What sql statement I have to use?
Thanks
This is a very good question, one that many forgets to ask (until it's too late) =;o)
1) This is pretty easy. The answer is to use another format, one that cannot be misunderstood regardless of language or date settings. The most common is ssyymmdd (eg 20060203 for february third this year)
You can find an excellent article on the subject here.
How do I delimit/format dates for database entry?
http://www.aspfaq.com/show.asp?id=2023
2) The display of dates are controlled by converting the datetime to a string and supplying a style parameter for the output you wish to have. These can be found in BOL under the pargraph that discusses 'CONVERT'. At a quick glance it doesn't look like there's one that exactly matches what you want, but it's possible to use different parts and concatenate them together in order to make a 'custom' format.
select convert(char(10), getdate(), 103) + ' ' + convert(char(8), getdate(), 108)
.. is close, but the time is in 24hr format instead of AM/PM.
If you really must have AM/PM and a 12hr clock, there are formats that one could use, but the code to rip out those pieces would be a bit more complex.
/Kenneth
No comments:
Post a Comment