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.googlegro ups.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.

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.

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.

DATEADD

Hello,
Who can help me with this issue:
I've a database with a field "Value" that is of the data_type
nvarchar. There is also a field "Method" that has the value "DateTime"
if the content of the field "Value" has a date and time value in the
format "dd/mm/yyyy h:mm:ss".
If the record has the value "DateTime" in the "Method" field, then I
want to add 1 hour to the value in the "Value" field. I tried the
statement
UPDATE SoftCheck
SET [Value] = DATEADD(hh, 1, [Value])
WHERE (Method = 'FileDate')
but it gives me the error: "Arithmetic overflow error converting
expression to date type datetime". I tried different CAST and CONVERT
combination in the statement, but still get the error.
In fact it is a very simple action I want to perform. In my field the
value could be a date and time, but the datetype is a nvarchar. If the
value is a date and time, I want to add 1 hour since the summer/winter
hour change. Obviously this isn't so simple explained in a sql syntax
statement!?
Regards, Geert"Geerty" <geert.defevere@.roularta.be> wrote in message
news:4d1ada6.0503300426.36e699a4@.posting.google.com...
> Hello,
> Who can help me with this issue:
> I've a database with a field "Value" that is of the data_type
> nvarchar. There is also a field "Method" that has the value "DateTime"
> if the content of the field "Value" has a date and time value in the
> format "dd/mm/yyyy h:mm:ss".
> If the record has the value "DateTime" in the "Method" field, then I
> want to add 1 hour to the value in the "Value" field. I tried the
> statement
> UPDATE SoftCheck
> SET [Value] = DATEADD(hh, 1, [Value])
> WHERE (Method = 'FileDate')
> but it gives me the error: "Arithmetic overflow error converting
> expression to date type datetime". I tried different CAST and CONVERT
> combination in the statement, but still get the error.
> In fact it is a very simple action I want to perform. In my field the
> value could be a date and time, but the datetype is a nvarchar. If the
> value is a date and time, I want to add 1 hour since the summer/winter
> hour change. Obviously this isn't so simple explained in a sql syntax
> statement!?
Are you sure that everyone row with Method='FileDate' has a date string? I
can only get that same error message if there is a large integer used in the
DATEADD function (try 10000000). If the rows contained dates, albeit in a
format that SQL has trouble converting due to the dd/mm mm/dd orientation
issues, you'd get the following error:
"The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value."
Best to avoid using localised date formats and stick to the ISO format, as a
date such as 30/03/2005 (today in the UK) won't be converted by SQL if it
treats it as a US date (as the 3rd of the 30th month isn't valid, and you
get the above "out-of-range" error message).
First thing I'd do if I were you is try to find the row with the invalid
data in it.
Dan|||I've double checked the rows where the field "Method" has the value
'DateTime' and all have a date/time in the format dd/mm/yyyy h:mm:ss.
This is the short date and time notation from the OS.|||Geerty wrote on 30 Mar 2005 23:24:29 -0800:

> I've double checked the rows where the field "Method" has the value
> 'DateTime' and all have a date/time in the format dd/mm/yyyy h:mm:ss.
> This is the short date and time notation from the OS.
You had 'FileDate' as your Method expression match in your T-SQL, could that
have been the problem or was it just a typo?
It seems strange that you're getting that error message, and also having
problems using CONVERT. With the latter function you need to make sure you
use a conversion value that tells SQL that the date format is British.
You'll have problems with CAST as you'd need to use a date format that is
unambiguous, unlike the format you are using.
This should work to convert your dates to internal date format, add the
extra hour, and then convert back again. As the format you're using isn't
one of the built-in formats for Convert it gets a little messy.
UPDATE SoftCheck
SET [Value] = CONVERT ( nvarchar(10) , DATEADD ( hh, 1, CONVERT ( datetime ,
[Value] , 103 ) ) , 103 ) + ' ' + CONVERT ( nvarchar(8) , DATEADD ( hh , 1 ,
CONVERT ( datetime , [Value] , 103 ) ) , 108 )
WHERE (Method = 'DateTime')
If you still get an error, one (or more) of your [Value] rows is definitely
not in the date format you specified, or is something that SQL is evaluating
to a large integer.
There's probably a neater way to do this, but I've only just woken up :)
Dan