SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 what is wrong with my syntax?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Trininole
Yak Posting Veteran

USA
83 Posts

Posted - 05/02/2013 :  16:33:06  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4344 Posts

Posted - 05/02/2013 :  16:36:00  Show Profile  Reply with Quote
What makes you think it is a syntax problem?

What is the data type of uc.check_date?

Edited by - Lamprey on 05/02/2013 16:36:29
Go to Top of Page

Trininole
Yak Posting Veteran

USA
83 Posts

Posted - 05/02/2013 :  16:39:32  Show Profile  Reply with Quote
It is varchar(8)

Roger DeFour
Go to Top of Page

Christech82
Starting Member

Hungary
20 Posts

Posted - 05/02/2013 :  16:41:12  Show Profile  Reply with Quote
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...
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8679 Posts

Posted - 05/02/2013 :  16:49:58  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

Trininole
Yak Posting Veteran

USA
83 Posts

Posted - 05/02/2013 :  16:50:40  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3322 Posts

Posted - 05/02/2013 :  16:58:23  Show Profile  Reply with Quote
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

Edited by - James K on 05/02/2013 16:59:29
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000