Solved

convert varchar date to smalldate

Posted on 2008-10-27
9
649 Views
Last Modified: 2010-04-21
I am trying to convert "FundingDate" field to smalldatetime which currently is saved as a varchar(8) but getting this error

Msg 295, Level 16, State 3, Line 1
Syntax error converting character string to smalldatetime data type.

sql query
select convert(smalldatetime, fundingdate,101) as CommDateVALUE,
code,fundingdate,ssn,opendate from extl


0
Comment
Question by:fahVB
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 8

Expert Comment

by:srafi78
ID: 22813865
Converting datetime and smalldatetime Data
When converting to datetime, Microsoft® SQL Server" 2000 rejects all values it cannot recognize as dates (including dates earlier than January 1, 1753). You can convert datetime values to smalldatetime when the date is in the proper range (from January 1, 1900 through June 6, 2079). The time value is rounded to the nearest minute.

This example converts smalldatetime and datetime values to varchar and binary data types, respectively.

DECLARE @mydate_sm  SMALLDATETIME
SET  @mydate_sm  = '4/05/98'

SELECT  CAST(@mydate_sm AS VARCHAR) AS SM_DATE_VARCHAR
GO

DECLARE @mydate  DATETIME
SET @mydate     = '4/05/98'

SELECT  CAST(@mydate AS BINARY) AS DATE_BINARY
GO

Here is the result set:

(1 row(s) affected)

SM_DATE_VARCHAR                
------------------------------
Apr  5 1998 12:00AM            

(1 row(s) affected)

DATE_BINARY                                                    
--------------------------------------------------------------
0x0000000000000000000000000000000000000000000000008c3000000000

(1 row(s) affected)


See Also
0
 
LVL 8

Expert Comment

by:srafi78
ID: 22813912
See if this works
select Convert(varchar(20), getdate(), 107)
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22813969
You've likely got a value in your varchar column that cannot be converted to date, which is causing your error.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22813974
It can't be varchar(8) if it's format 101 because 101 is mm/dd/yyyy.  That's 10.  Perhaps it's format 1 which is mm/dd/yy?

Things like:

DECLARE @mydate  DATETIME
SET @mydate     = '4/05/98'

Are not a good thing to do because depending upon regional settings, it can interpret mm/dd/yy and dd/mm/yy differently.  Which are both valid in this context.

It's always bet to specify
mm/dd/yy = format 1
dd/mm/yy = format 3
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:fahVB
ID: 22814035
I saw one blank record, no data and mostly its in this format 11/09/07

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22814180
But is that mm/dd/yy or dd/mm/yy?

And mostly won't work.  You have to have a consistent format in order to ensure proper parsing.
0
 

Author Comment

by:fahVB
ID: 22814190
all of it is mm/dd/yy
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 250 total points
ID: 22814212
mm/dd/yy is format 1.


select convert(smalldatetime, fundingdate,1) as CommDateVALUE
   ,code
   ,fundingdate
   ,ssn
   ,opendate
from extl
where isdate(fundingdate)=1

0
 

Author Closing Comment

by:fahVB
ID: 31510367
sweet, it worked..Thank you
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now