what is wrong with my syntax? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

what is wrong with my syntax?

Author

Topic

Trininole
Yak Posting Veteran

83 Posts

Posted - 2013-05-02 : 16:33:06

Here is the syntax i am using in my code:

min(convert(datetime2,uc.check_date,110))

and the error message that i am getting is this

Msg 241, Level 16, State 1, Line 8
Conversion failed when converting date and/or time from character string.

Roger DeFour

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-05-02 : 16:36:00

What makes you think it is a syntax problem?

What is the data type of uc.check_date?

Trininole
Yak Posting Veteran

83 Posts

Posted - 2013-05-02 : 16:39:32

It is varchar(8)

Roger DeFour

Christech82
Starting Member

20 Posts

Posted - 2013-05-02 : 16:41:12

quote:
Originally posted by Lamprey

What makes you think it is a syntax problem?

What is the data type of uc.check_date?

If you want to insert data in your DATE column, the data type should be DATE rather than VARCHAR...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-05-02 : 16:49:58

there is a Problem in your data.
your convert() with style 110 means the incoming data is 'mm-tt-jj'.
check if there is data with different Format.

Too old to Rock'n'Roll too young to die.

Trininole
Yak Posting Veteran

83 Posts

Posted - 2013-05-02 : 16:50:40

when i used date, i got the same error msg. I am not sure what the correct syntax is so that is why i was asking?

Roger DeFour

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-02 : 16:58:23

quote:
Originally posted by Christech82

quote:
Originally posted by Lamprey

What makes you think it is a syntax problem?

What is the data type of uc.check_date?

If you want to insert data in your DATE column, the data type should be DATE rather than VARCHAR...

Or, VARCHAR (or another character datatype) that has a string literal that can be converted to one of the datetime data types. For example

CREATE TABLE #tmp (check_date VARCHAR(10));
INSERT INTO #tmp VALUES ('12-25-2012'),('11-19-2013')

SELECT MIN(convert(datetime2,uc.check_date,110)) FROM #tmp uc;
DROP TABLE #tmp;

The problem you are running into is that you are specifying the style to be 110. Take a look at this page: http://msdn.microsoft.com/en-us/library/ms187928.aspx Style 110 requires that you specify the date exactly in the mm-dd-yyyy format. That requires 10 characters. Yet your column is only 8 characters wide. So it will always fail.

Take a look at the data in your check_date column. See what format it is in. Also try running this:

SELECT * FROM YOURTABLE WHERE ISDATE(check_date) = 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources