formating datetime field in sql server 2005

[cgtalk]

hi

i have a problem regarding datetime field in sql server 2005 which is as follows:

i have used this query inside c# code which i used to grab some float values that are between two date values

select a from b where a='" + value1 + "' and c='" + value2 + "') and date>= '" + from + "' and date<='" + to + "' ";


the problem is that the date time values that i'm comparing with is in this format
dd/mm/yy

but the format field that is in the sql date column is being treated as mm/dd/yy

so i want to know is there any way that i can convert this date schema insode sql server 2005 to match this format dd/mm/yy

this is an example of what is currently happends:

from ="01/02/2008"; // 1st of feburary it's a datetime c# value dd/mm/yy
to ="01/04/2008";

// inside sql server table there is a column which holds datetime value
date
01/02/2008 // it's being treated as 2nd of january
01/03/2008
01/04/2008

so when executing query no result came becouse all the months are being treated as january.
i over come this by changing the day and month values inside c# code
but that doesn't make sense becouse those values will be stored also inside another table.


i hope to find an answer to this

thanks

[Leon.Kennedy]

I think the easiest thing to do would be to format the date in c# to be the format that SQL Server sees it as. That way you don't have to worry about date formats in SQL Server.

[Alsvha]

In SQL you can cast a string to a datetime and provide a "format" code (basically like you can in C#).
Take a look at this: http://msdn.microsoft.com/da-dk/library/ms187928(en-us).aspx

The one you appear to be looking for is would be:
convert(datetime, '24/12/08', 3)
which converts 24th of December 2008 to a datetime.

So your example would be something akin to (if I read your syntax correct)

select a from b where a='" + value1 + "' and c='" + value2 + "') and date>= cast(datetime, '" + from + "', 3) and date<= cast(datetime,'" + to + "',3) ";