Solved

convert varchar date to smalldate

Posted on 2008-10-27
9
647 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

708 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

17 Experts available now in Live!

Get 1:1 Help Now