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.

DateDiff + Where am I going wrong?

Hi,

I'm creating a sample report in Visual Studio 2005 to be used in SSRS 2005.

I'm attempting to do some conditional formatting like so:

=IIF(DateDiff("Day", Fields!Jobseeker.Last Interview.Value, NOW()) > 14, "Red", "Transparent"

When ever I try and preview the report, VS throws a wobblie and closes down. The format of the Last Interview column is dd/mm/yyyy.

Any suggestions

Thanks, Steve

Hi, Steve,

Use VB parameters to DateDiff - ... try 'd' instead of "Day".

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctdatediff.asp

Also verify that the "Last Interview" field name has an underscore "Last_Interview" - that should have happened automatically when you created a dataset from the query.

_

This posting is provided "AS IS" with no warranties, and confers no rights.

DATEADD Issue

I am attempting to automate a monthend snapshot of my data, and the
following code works when I run it from a query window in SQL 2005, but it
generates and error message when I schedule it as a job. The error I get
is...
Msg 1023, Level 15, State 1, Line 10
Invalid parameter 1 specified for dateadd.
I have tried the code below using "Month", "M", 'Month', 'M', 'mm', and more
as the parameters for DATEADD, but all return the same error from job
execution. I'm sure that I'm just missing something simple, but its driving
me crazy.
IF Day(GETDATE()) = 1
BEGIN
DECLARE
@.NewName VARCHAR(12),
@.Current VARCHAR(12),
@.DataFile VARCHAR(100),
@.LogFile VARCHAR(100),
@.ThisDate DATETIME
SELECT @.ThisDate = DATEADD("Month", -1,GETDATE())
SELECT @.NewName = 'BIDW_' + CONVERT(VARCHAR(4),Year(@.ThisDate)) + '_' +
RIGHT('0' + CONVERT(VARCHAR(2),Month(@.ThisDate)),2)
SELECT @.Current = 'BIDW_' + CONVERT(VARCHAR(4),Year(GETDATE())) + '_' +
RIGHT('0' + CONVERT(VARCHAR(2),Month(GETDATE())),2)
SELECT @.DataFile = 'D:\SQLServer2005\Databases\BIDW\' + @.Current +
'_Data.mdf'
SELECT @.LogFile = 'D:\SQLServer2005\Databases\BIDW\' + @.Current +
'_Log.ldf'
EXEC sp_renamedb 'BIDW_Monthend', @.NewName
USE BIDW
RESTORE FILELISTONLY
FROM BIDW_on_Domino1
RESTORE DATABASE BIDW_Monthend
FROM BIDW_on_Domino1
WITH RECOVERY,
MOVE 'BIDW' TO @.DataFile,
MOVE 'BIDW_log' TO @.LogFile
ENDWhenever you are stuck on a problem, just post the problem to everyone in a
newsgroup and within 2 minutes of posting the question, you will realize the
answer. Nevermind everyone, I figured it out. The first part of the DATEADD
parameter needs no quotes at all.
-Brian
"Brian VanDyke" <brianv@.community.nospam> wrote in message
news:%23wcLUNSaGHA.4144@.TK2MSFTNGP04.phx.gbl...
>I am attempting to automate a monthend snapshot of my data, and the
>following code works when I run it from a query window in SQL 2005, but it
>generates and error message when I schedule it as a job. The error I get
>is...
> Msg 1023, Level 15, State 1, Line 10
> Invalid parameter 1 specified for dateadd.
> I have tried the code below using "Month", "M", 'Month', 'M', 'mm', and
> more as the parameters for DATEADD, but all return the same error from job
> execution. I'm sure that I'm just missing something simple, but its
> driving me crazy.
> IF Day(GETDATE()) = 1
> BEGIN
> DECLARE
> @.NewName VARCHAR(12),
> @.Current VARCHAR(12),
> @.DataFile VARCHAR(100),
> @.LogFile VARCHAR(100),
> @.ThisDate DATETIME
> SELECT @.ThisDate = DATEADD("Month", -1,GETDATE())
> SELECT @.NewName = 'BIDW_' + CONVERT(VARCHAR(4),Year(@.ThisDate)) + '_' +
> RIGHT('0' + CONVERT(VARCHAR(2),Month(@.ThisDate)),2)
> SELECT @.Current = 'BIDW_' + CONVERT(VARCHAR(4),Year(GETDATE())) + '_' +
> RIGHT('0' + CONVERT(VARCHAR(2),Month(GETDATE())),2)
> SELECT @.DataFile = 'D:\SQLServer2005\Databases\BIDW' + @.Current +
> '_Data.mdf'
> SELECT @.LogFile = 'D:\SQLServer2005\Databases\BIDW' + @.Current +
> '_Log.ldf'
> EXEC sp_renamedb 'BIDW_Monthend', @.NewName
> USE BIDW
> RESTORE FILELISTONLY
> FROM BIDW_on_Domino1
> RESTORE DATABASE BIDW_Monthend
> FROM BIDW_on_Domino1
> WITH RECOVERY,
> MOVE 'BIDW' TO @.DataFile,
> MOVE 'BIDW_log' TO @.LogFile
> END
>