datetime query

my database contains 1 field with "datetime" in sql server
hw can i query the database to just compare the date section of that field ? and not the time
WHERE CONVERT(VARCHAR(10),datecolumn,101) >= '08/06/2005'
|||Although Dinakar's suggestion will work, it will not perform as well as something like this:

WHERE datecolumn >= '20050806' AND datecolumn < '20050807'

When you perform a function on a column (such as CONVERT) this willmake the query non-sargable and will therefore not take advantage of anindex on the column.
For more tips on performance, seeSQL Server Transact-SQL WHERE Clause.

datetime function with no time component?

Hi All,

When I compare dates but I want to ignore the time within the datetime I find myself doing this:

CONVERT(int, CONVERT(char(8), @.MyDate, 112))

style 112 is yyyymmdd

int is very predictable for comparisons, and performs well too.

It works but it is not readable, especially if you have several of these expressions in the same WHERE clause or CASE stmt. I also tried a udf but that has its own reusability problems across dbs and projects.

Is there a cleaner way to do this with a system function?

Carl

If you just want to compare dates, ignoring times, you could use the datediff function:

WHERE datediff( day, MyFirstDate, MyOtherDateTime ) = 0

For example:

Code Snippet

SELECT
Match = CASE
WHEN datediff( day, '2007/07/07 08:45 AM', getdate() ) = 0
THEN 'Match -Same Day'
ELSE 'Bummer! -No Match'
END,
NoMatch = CASE
WHEN datediff( day, '2007/07/06 08:45 AM', getdate() ) = 0
THEN 'Same Day'
ELSE 'Different Day'
END

Match NoMatch
-- -
Match -Same Day Different Day

DATEDIFF(), using the 'day' parameter, verifies that the two values are the same date IF there is NO difference [ = 0 ].

|||

Thanks Arnie,

For = and != logic, this is cleaner.

Not much of an improvement in readability for >, < , !>, and !< type comparisons

Carl

|||

And not too good for performance either.

While using the datediff() process 'looks' good, or as you said, 'cleaner', performance, related to other methods, can be disasterous. It will require at 'best', a clustered index scan. Actually, unless there is an index on the datetime column, it has to scan the entire table -which is what a 'clustered index scan' really is.

Compare that with the second option, my preferred method, of using date values in the criteria.

Code Snippet

USE Northwind
GO

SELECT *
FROM Orders
WHERE datediff( day, OrderDate, '1996/08/27' ) = 0

SELECT *
FROM Orders
WHERE ( OrderDate >= '1996/08/27'
AND OrderDate < '1996/08/28'
)

If you examine the execution plans, you will notice the method using the datediff() takes 19 times as long to execute since it has to scan the entire table.

DateTime format

I'm using shortdate().
The problem is if I enter a date 01/24/2007 12:00:00 AM,
I get the result as 1/24/2007 and not 01/24/2007.
Hence when I compare dates, there will not be any resulting data for the above given date?

What should I do to get the shortdate as 01/24/2007?Shortdate... So this is an Access question and not a SQL Server question?

DateTime comparison with some exceptions

I have StratDateTime and EndDateTime fields in the table. I need to compare this two datetime fields and find seconds. I can use DateDiff but there are the following exceptions:

1. Exclude seconds coming from the date which are Saturday and Sunday

2. Exclude seconds coming from time range between 7:01pm and 6:59am

3. Exclude seconds coming from Jan 1^st and Jul 4^th.

So do you want to make the difference between the two columns in seconds a column in the query results or do you want to compare them to each other or some other values in the WHERE clause of the query? I'd suggest that you post the query you have written and then one of us can help you with the query, as it is you have not really posted enough information for us to help you.|||

Ok. Thank you very much for your response.

SELECT Datediff(ss,StartDateTime,EndDateTime) AS mySeconds

FROM MyTable

This will return seconds. However this does not hold all the exceptions I listed above. Let’s say I have StartDateTime=12/15/2006 7:00pm and EndDateTime=12/18/2006 9:00am, then the difference should be 2 hours because between 12/15/2006 7:00pm and 12/18/2006 7:00am is not a business period, the rest is 2 business hours.

|||This is the same question asked by you before

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1022431&SiteID=1

I will again suggest you to use the calender-table. This will make life easy as you are not able to know 12/16/2006, is Saturday or working day.
With calender-table you can easily find that, more over you can create the holiday list too, find the difference between any date & more functionality can be add according to your own requirements.

Gurpreet S. Gill|||

Thank you very much for your help. That does not work for me since it is considering the day, not the time. My business day should be between 7:00am and 7:00pm in the weekdays. I do not see how getting number of business days would really help.

I would ask the same question, let’s say I have a calendar table, how would I get calendar table return me 2 hours for the following example. I have StartDateTime=12/15/2006 7:00pm and EndDateTime=12/18/2006 9:00am, then the difference should be 2 hours because between 12/15/2006 7:00pm and 12/18/2006 7:00am is not a business period, the rest is 2 business hours.

Thanks you very much for your help.

|||

What you need to do is create a user defined function to calculate your desired value. It will look like this, I haven't put in all the conditional code for you, that will take a while, but you get the idea.

CREATE FUNCTION BusinessSeconds(@.StartTime datetime, @.EndTime datetime)
RETURNS int
AS
BEGIN
DECLARE @.retVal int
SET @.retVal = datediff(ss, @.StartTime, @.EndTime)
--Conditional code here to subtract your non-business periods
--eg. IF ... SET @.retVal = @.retVal - 86400
RETURN @.retVal
END

And you'll use it like this

SELECT dbo.BusinessSeconds(StartDateTime, EndDateTime) AS MySeconds
FROM MyTable

DATETIME compare issue

I am trying to do a select where one set of date/time columns are greater than another. My date is stored in one column while the time is stored in another. Only the date portion of the date column is valid and only the time portion of the time column is valid.
Example my date column value is 8/1/2007 01:01:01 AM and my time column value is 1/1/2007 07:23:49 AM which in my system means the last update time was 8/1/2007 07:23:49 AM.

My select statment looks like this.

SELECT *
FROM CHANGE
WHERE
CONVERT(DATETIME( DATE_LAST_ALTERED, TIME_LAST_ALTERED)) < CONVERT(DATETIME(DATE_APPROVED,TIME_APPROVED))

I get an incorrect syntax near 'DATE_LAST_ALTERED'.

Am I totaly missing the point of DATETIME?

Matt

You need to give a look to the CAST AND CONVERT article in books online; your syntax for your CONVERT function is not correct. And yes, you might very well be missing the point of date and time in SQL Server. You should normally store date and time in a single column. And your usage here surely indicates that your date and time should be stored in a single column. You might be able to run with a where clause something like:

Code Snippet

where cast(floor(cast(date_last_altered as float)) as datetime)
+ time_last_altered
- floor(cast(time_last_altered) as float))
< cast(floor(cast(date_approved as float)) as datetime)
+ time_approved
- floor(cast(time_approved) as float))

Will someone please check me please?

|||Kent,

Thanks for the quick response. I was going to put a comment in about the fact that the 2 columns to store the data was not my doing and that I am stuck with it; knowing I would get that comment in response.

Apparently in DB2 DATETIME(col1,col2) is supported so this has never been an issue. I am also stuck with constraints on the length of my WHERE statement. (Imposed by the application that is taking the WHERE statement and storing it.)

Looks like I wil have to figure another way.

Thanks again.
Matt
|||

Very well; is this then a DB2 question and not a SQL Server question?

|||Can you modify/add a column to the database then? I would suggest u make a varchar column and combine the 2 columns, otherwise u may have to do a lot of number crunching due to ur where clause constraints - what is the exact contraint?

datetime compare - eastern and pacific

Hi,
I need to compare two datetime values, one is eastern and anther is pacific. Is there an existing function that I can turn a pacific time to eastern one? or I have to write it by myself?
Thanks,
Liliyou might try using the dateadd function to manipulate the time by +2 hours.|||It works! Thank you.

In case someone wanna know, I used:
SET @.NEWSTART = DATEADD(HH, 3, OLDSTARTTIME)

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.