September 17, 2009 at 12:11 pm
Hi!
I have this command:
SELECT *
FROM MESSAGE
WHERE ((ID_STUDENT = 10969)
OR (ID_STUDENT = 9999999))
AND DT_BEGIN <= (SELECT CONVERT(CHAR(10), GETDATE(), 110))
AND DT_END >= (SELECT CONVERT(CHAR(10), GETDATE(), 110))
That command recovers data from this table:
CREATE TABLE [dbo].[MESSAGE](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TYPE] [char](1) NOT NULL,
[ID_STUDENT] [int] NULL,
[DT_BEGIN] [date] NOT NULL,
[DT_END] [date] NOT NULL,
[MESSAGE] [nvarchar](2000) NOT NULL,
[BLOCK] [char](1) NOT NULL,
[REPEAT] [nchar](1) NOT NULL,
[VIEW] [nchar](1) NOT NULL)
Obs.: I removed index, keys, relationships from declaration...
My problem is that SELECT works fine in some servers but doesn't work in other servers: I used the same client to connect to two differente servers: the SELECT has worked fine on the first server but give me a error on the second. I'm using the same configuration for both server.
The error is (Brazilian Portuguese):
Msg 241, Level 16, State 1, Line 2
Falha ao converter data e/ou hora da cadeia de caracteres.
In english, it will be something like this:
Msg 241, Level 16, State 1, Line 2
Failed to convert date/time string
The error appears be the line:
SELECT CONVERT(CHAR(10), GETDATE(), 110)
But this line, when I run it ALONE, separeted from the whole code, works fine on both server.... but when I run whole code with this line it fails...
Why is it happening? How do I fix this?
September 17, 2009 at 12:15 pm
Sorry, a forgot to say: I'm using the line
SELECT CONVERT(CHAR(10),GETDATE(),110)
to get JUST THE DATE, without the time.
If there's another way to get just the date please, tell me...
September 17, 2009 at 1:23 pm
Likely you have an implicit conversion occurring somewhere. If you change to
month( dt_begin ) = month( getdate))
and day (dt_begin) = month(getdate())
and year( dr_begin) = year( getdate())
does that work?
I'm leaning towards the data somehow causing you an issue.
September 17, 2009 at 1:41 pm
Are both instances running SQL Server 2008?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 18, 2009 at 10:13 am
Yes, both versions are SQL Server 2008 Express
September 18, 2009 at 10:56 am
This is probably caused by conflicts between you SQL Server's SET LANGUAGE defaults, the Servers, region/national settings and your use of format 110 which is very non-standard.
Notice that in your compares, you are *explicitly* converting getdate() to a string using format 110 and comparing that to a date value (DT_BEGIN, DT_END). That comparison requires an implicit conversion in order to resolve their type differences (datetime versus nvarchar) and you do NOT want to be mixing implicit and explicit conversions like that, especially with datetime values.
If you must do this then either explicitly convert them both to nvarchars OR explicitly convert getdate() to an nvarchar and then back to a date:
SELECT *
FROM MESSAGE
WHERE ((ID_STUDENT = 10969)
OR (ID_STUDENT = 9999999))
AND DT_BEGIN = (SELECT CONVERT(DATETIME, CONVERT(CHAR(10), GETDATE(), 112)))
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 18, 2009 at 10:59 am
Note my use of Format 112, which is the unambiguous ISO Date-only standard format. If you want to play date-only string games (as opposed to all explicit conversions, like I did above), it's the preferred way to go.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 23, 2009 at 9:14 am
SQL 2008 added the date data type, which should accomplish what you are trying to do.
SELECT *
FROM MESSAGE
WHERE ((ID_STUDENT = 10969)
OR (ID_STUDENT = 9999999))
AND DT_BEGIN = (SELECT CONVERT(DATE, GETDATE()))
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply