July 6, 2010 at 10:11 pm
Hello,
I am in Australia and we use English style for dates.
On the web form the use enters the date for example: '02/07/2010 12:12:00.000'
But when I query with this date in the database it treat 02 as month. What I want is 02 should be treated as day and 07 as month.
Kindly Advice.
Thanks.
July 7, 2010 at 1:03 am
Dates have many formats. There is a default format that is used on the server level, but this can be overwritten by login’s settings and session’s settings. Because of that, it is a good practice to make sure that the date that you send to the server can be interpreted in exactly one way. There are 2 ways to do so. One way is to use set dateformat statement (and of course make sure that you send the date according to this format). Here is a small example (notice that the same string was treated different each time according to the format that I used):
set dateformat dmy
set @MyDate = '07/02/2010'
select @MyDate
set dateformat mdy
set @MyDate = '07/02/2010'
select @MyDate
Another way is to use a format that regardless of the way that the server/user/session is configured, the server will know exactly how to intepreted the string. Such a format is ISO format. You can use a string in the fallowing structure YYYYMMDD. Make sure that you use it as a string and not a number. Here is an example:
declare @MyDate datetime
select @MyDate = '20100702'
select @MyDate
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 7, 2010 at 4:45 am
if you are sending this date from the front end(i.e. .net, php) then always send you date in "yyyy-MM-dd" or "yyyy-MMM-dd hh:mm:ss" if you want time also. it will never be wrong.
and if you are fetching date from the database then use
SELECT Convert(VARCHAR, GETDATE() ,101)
SELECT Convert(VARCHAR, GETDATE() ,107)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply