Last post Feb 12, 2010 05:10 AM by aresssrinivas
Feb 11, 2010 12:14 PM|-_-|LINK
how can i retrieve a datetime field which is in specific date range, which is string format 'dd/mm/yyyy' in SQL using MS SQL Server.
select return_date from AAA where return_date >= '01/02/2010 ' and <='08/02/2010'
I can retrieve the correct daterange only with this format:
Convert(varchar(10),'2010-02-08',120) , but i want the format to retrieve date is
how can i do this?
Feb 11, 2010 12:44 PM|ignatandrei|LINK
case when len(day(return_date)) =1 then '0' + cast(day(return_date) as varchar)
else cast(day(return_date) as varchar) end
+ '/' +
case when len(month(return_date) =1 then '0' + cast(month(return_date) as varchar)
else cast(month(return_date) as varchar) end
+ '/' +
+ year(return_date) from AAA where ...
Feb 11, 2010 01:09 PM|vijjendra|LINK
you can do this as follows:
SELECT CONVERT(VARCHAR(10), return_date, 103) AS [return_date]
from AAA where return_date >= '01/02/2010 ' and <='08/02/2010'
For more go here
Feb 11, 2010 01:13 PM|Imran khan|LINK
here two variables of type DateTime that contains the range of Dates
Now you can use the code
select convert(varchar,Return_Date,103) as Return_Date from bm_category where Return_Date > new DateTime(From.Year,From.Month,From.Day) and Return_Date < new DateTime(To.Year,To.Month,To.Day)
1: 103 means your formate is dd/mm/yyyy
2: it doesnt matter your formate of two range dates.you genralize it at the time of comparison like (new DateTime(To.Year,To.Month,To.Day))
Feb 11, 2010 02:13 PM|-_-|LINK
I already covert to 103, it work properly in my PC, but in other PC, it need to retrieve date format with 'mm/dd/yyyy' even i set the localale time in that PC
Feb 12, 2010 04:59 AM|Imran khan|LINK
dont worry about the formates of dates its very very simple.
just note the following points
1: no matter in which formate your date is stored in sql server(Data Base)
2: whenever you compare the DateTime fielde use the method that i have already tell you in previou reply.
3: now the last thing is to convert it into proper formate just for the representation purpose ,you have to use theses codes at the time of SELECt statement.
4: so,in your case tou just send the code 103 for dd/mm/yyyy and 101 for mm/dd/yyyy
5: never ever change the location of pc that you do,because in this way yo depend upon the location that is not feasuble.
6: and the better way is that you place your code(101,103) in App.config file(in case of Windows form application) and in web.config(in case of web applications).
7: in all SELECT statement you get code from ubove two files.
8: whenever you need to change the formate you just change the in one of the above files.thats it....
i hope you understand well
Feb 12, 2010 05:10 AM|aresssrinivas|LINK
I assume that you are using .net framework to connect to database:
So just set the culture to en-GB under system.web settings in web.config....
This would mean that you would not have to change anything if you are porting your code to a different which has a different system culture set.
<globalization culture="en-GB" uiCulture="en-GB"/>
And when you pass the date value to the query pass it as a date and not string.....
Hope this helps.............