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
Solved

convert varchar date to smalldate

Posted on 2008-10-27
9
651 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
backup job space check 4 44
t-sql need help on t-sql 10 27
SQL, add where clause 5 24
Use column to search string column 2 11
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

828 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