DATEDIFF and time format in Sql Server

Hello;
I'm attempting to use the datediff method to compare two dates,
generated under visual studio 2005 with the instruction
DateTime.Now.ToLocalTime().ToString(), which returns something like DD-
MM-YYYY HH:MM:SS.
the dates are then stored in an sql server database and then a query
returns some results based on the difference between two given dates
using the datediff instruction.
the problem is that SQL Server interprets the time as being MM-DD-YYYY
instead of DD-MM-YYYY, which means an query like
SELECT DATEDIFF(month, '11-2-2007 11:11:11', '12-4-2007
11:11:11') AS Expr1
FROM <table>
will return 1 instead of 2.
the sql server 2005 i'm using the the one that comes with VS2005, it's
not the stand alone version. i've tried looking into some settings
hoping to fix this, but i've had no luck this far.
how can i change the way sql server reads a date, or how can i "fool"
him using some other method?
thanks in advance!A quick fix for this would be to use SET DATEFORMAT to change the current
interpretation of character strings when they are converted to date values.
Something like this:
SET DATEFORMAT dmy
GO
SELECT DATEDIFF(month, '11-2-2007 11:11:11', '12-4-2007 11:11:11')
That should give you as result 2, which is what you expect. Alternatively
you can use SET LANGUAGE which will set the format according for the
language selected.
However, the correct way to fix this is:
1. In your Visual Studio application pass the date to SQL Server as a Date
data type (not string)
2. In SQL Server store the date in a datetime column type
That way dates will be always treated properly, plus you can benefit of
using the date/time functions directly with no conversion.
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Thank you for the answer!
There's more than one solution, and i'm pleased with that already!
But if the Datetimes provided by Datetime.Now.ToLocalTime() are in the
DD-MM-YYYY format, even if i store them as Datetime in the database,
won't the problem remain still? I always have to compare the dates
within the database with those provided by that instruction...
Unless i'm making some confusion in my head, datediff always uses
(unless i use that other suggestion) MM-DD-YYYY over DD-MM-YYYY,
regardless if it's stored as datetime or string, right? I don't want
to compare two dates within the database, but alwas between a stored
value and a current value (from the c# 's datetime).
The actual instruction (without your suggested changes) is something
like:
SELECT <titles> FROM <table> WHERE <conditions> AND (datediff(second,
<date stored>,'" + DateTime.Now.ToLocalTime().ToString() + "')>20)
Thanks once again!
On Mar 15, 3:02 am, "Plamen Ratchev" <Pla...@.SQLStudio.com> wrote:
> A quick fix for this would be to use SET DATEFORMAT to change the current
> interpretation of character strings when they are converted to date values
.
> Something like this:
> SET DATEFORMAT dmy
> GO
> SELECT DATEDIFF(month, '11-2-2007 11:11:11', '12-4-2007 11:11:11')
> That should give you as result 2, which is what you expect. Alternatively
> you can use SET LANGUAGE which will set the format according for the
> language selected.
> However, the correct way to fix this is:
> 1. In your Visual Studio application pass the date to SQL Server as a Date
> data type (not string)
> 2. In SQL Server store the date in a datetime column type
> That way dates will be always treated properly, plus you can benefit of
> using the date/time functions directly with no conversion.
> HTH,
> Plamen Ratchevhttp://www.SQLStudio.com|||"zainab" <pedralm@.gmail.com> wrote in message
news:1173930670.588966.235330@.o5g2000hsb.googlegroups.com...
> Thank you for the answer!
> There's more than one solution, and i'm pleased with that already!
> But if the Datetimes provided by Datetime.Now.ToLocalTime() are in the
> DD-MM-YYYY format, even if i store them as Datetime in the database,
> won't the problem remain still? I always have to compare the dates
> within the database with those provided by that instruction...
> Unless i'm making some confusion in my head, datediff always uses
> (unless i use that other suggestion) MM-DD-YYYY over DD-MM-YYYY,
> regardless if it's stored as datetime or string, right? I don't want
> to compare two dates within the database, but alwas between a stored
> value and a current value (from the c# 's datetime).
> The actual instruction (without your suggested changes) is something
> like:
> SELECT <titles> FROM <table> WHERE <conditions> AND (datediff(second,
> <date stored>,'" + DateTime.Now.ToLocalTime().ToString() + "')>20)
>
Ok, this makes things different. In C# I believe you can do something like
this:
DateTime.Now.ToLocalTime().ToString("MM/dd/yyyy HH:mm:ss")
That should format the date/time to match the current SQL Server format.
A better solution will be to create a stored procedure with datetime
parameter and to pass the date from C# as datetime, like
DateTime.Now.ToLocalTime() without converting to string. Then as long as the
column of the table in SQL Server is datetime type you do not have to worry
about the format of the date. Datetime type is compatible and will always be
interpreted correctly.
Regards,
Plamen Ratchev
http://www.SQLStudio.com|||> Unless i'm making some confusion in my head, datediff always uses
> (unless i use that other suggestion) MM-DD-YYYY over DD-MM-YYYY,
> regardless if it's stored as datetime or string, right?
Wrong. Datetime values are not stored in ANY readable format. If you
intend to represent datetime constants as strings in your tsql code (either
directly or indirectly via the code/functions generated/provided by VS),
then you should understand how these strings are interpreted and how to use
them correctly.
http://www.karaszi.com/sqlserver/info_datetime.asp|||Thank you both for your replies!
By using a simple "SET DATEFORMAT dmy" before my instruction, as
suggested by Plamen Ratchev, i had my problem instantly fixed. I didnt
have to change the table settings as this is the only use i give to
this field (besides presenting the value, where keeping it as a string
made it simpler for me).
According to Scott Morris' link:
The Numeric format (the one i was using) can use dash (-), dot (.) or
slash (/) as separator. The rules for how SQL Server parses the string
doesn't change depending on the separator. A common misconception is
that the ANSI SQL format (sometime a bit incorrectly referred to as
the "ISO format"), 1998-02-23, is language neutral. It isn't. It is a
numeric format and hence it is dependent on the SET DATEFORMAT and SET
LANGUAGE setting
SET DATEFORMAT inherits its setting from SET LANGUAGE (but an explicit
SET DATEFORMAT will override later SET LANGUAGE).
so it was pretty clear that all i had to do was indeed SET DATEFORMAT
dmy!
thank you!
ps: sorry for the "explanation", but sometimes it's useful in the
future for people who run into the same problems.