Solved

convert varchar date to smalldate

Posted on 2008-10-27
9
650 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
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.

 
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
 

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

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.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

778 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