Solved

convert varchar date to smalldate

Posted on 2008-10-27
9
652 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Regarding Disk IO 3 49
T-SQL Query - Group By Year 3 32
Trigger C# code inside the SQL Server 6 36
denied execute as 13 33
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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.
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.

730 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