+ Reply to Thread
Results 1 to 8 of 8

Thread: varchar date problem

  1. #1
    todd2006
    todd2006 is offline
    Barn Loyal todd2006 is an unknown quantity at this point todd2006's Avatar
    Join Date
    Mar 2008
    Posts
    889
    Rep Power
    4

    varchar date problem

    Hello,

    I have this field called std_date as varchar that stores dates in it

    for example one of the records has date stored like this

    04/10/2012

    I have this query
    Code:
    strsql="Select * from students where  month(std_date) <= Month(getdate()) and year(std_date)=year(getdate()) order by Id
    database is sql

    it gives an error conversion failed when converting datetime from character string

    any idea whats going wrong

    thanks

    todd
    todd2006, May 3rd, 2010 03:29 PM
    Reply With Quote Reply With Quote
  2. #2
    mehere
    mehere is offline
    Super Sarcasm Mistress mehere is a glorious beacon of light mehere is a glorious beacon of light mehere is a glorious beacon of light mehere is a glorious beacon of light mehere is a glorious beacon of light mehere's Avatar
    Join Date
    Mar 2008
    Location
    Wide Awake In Dreamland
    Posts
    830
    Rep Power
    8

    you can't use things like month/year on varchar fields. since it doesn't see it as a date field, it doesn't know what you're doing. this is the reason why date fields should be indicated as such in the database. try something like this:
    Code:
    month(convert(smalldatetime,std_date)) <= Month(getdate()) 
and year(convert(smalldatetime,std_date))=year(getdate()) 
order by Id
    mehere, May 3rd, 2010 03:39 PM
    Last edited by mehere; May 3rd, 2010 at 03:57 PM. Reason: corrected convert function ... originally had it backwards.
    Quote of the Month:
    INSIGHT: When the going gets tough, the tough get going. The smart left a long time ago.

    Questions to Ponder:
    Are people more violently opposed to fur rather than leather because it's much easier to harass rich women than motorcycle gangs?

    iif([sarcasm]=true,iif([you have to ask]=true,"didn't work","ha ha ha"),"not sarcasm")
    copyright © 2008 sbenj69

    Sarchasm: The gulf between the author of sarcastic wit and the person who doesn't get it.
    Reply With Quote Reply With Quote
  3. #3
    todd2006
    todd2006 is offline
    Barn Loyal todd2006 is an unknown quantity at this point todd2006's Avatar
    Join Date
    Mar 2008
    Posts
    889
    Rep Power
    4

    IT gives an error invalid column name smalldatetime
    todd2006, May 3rd, 2010 03:46 PM
    Reply With Quote Reply With Quote
  4. #4
    jmurrayhead
    jmurrayhead is offline
    The Barnfather jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead's Avatar
    Join Date
    Mar 2008
    Location
    Reston, VA
    Posts
    4,547
    Blog Entries
    9
    Real Name
    Jason
    Rep Power
    22

    should be:
    Code:
    convert(smalldatetime, std_date)
    jmurrayhead, May 3rd, 2010 03:51 PM
    jmurrayhead
    If you agree, give me rep.
    If you like it here...throw us a few bones to help support us.

    Reply With Quote Reply With Quote
  5. #5
    todd2006
    todd2006 is offline
    Barn Loyal todd2006 is an unknown quantity at this point todd2006's Avatar
    Join Date
    Mar 2008
    Posts
    889
    Rep Power
    4

    jason its giving an error

    conversion failed when converting character string to smalldatetime data type
    todd2006, May 3rd, 2010 03:56 PM
    Reply With Quote Reply With Quote
  6. #6
    mehere
    mehere is offline
    Super Sarcasm Mistress mehere is a glorious beacon of light mehere is a glorious beacon of light mehere is a glorious beacon of light mehere is a glorious beacon of light mehere is a glorious beacon of light mehere's Avatar
    Join Date
    Mar 2008
    Location
    Wide Awake In Dreamland
    Posts
    830
    Rep Power
    8

    Quote Originally Posted by jmurrayhead View Post
    should be:
    Code:
    convert(smalldatetime, std_date)
    realized that i had it backwards ... thanks j.
    mehere, May 3rd, 2010 03:56 PM
    Quote of the Month:
    INSIGHT: When the going gets tough, the tough get going. The smart left a long time ago.

    Questions to Ponder:
    Are people more violently opposed to fur rather than leather because it's much easier to harass rich women than motorcycle gangs?

    iif([sarcasm]=true,iif([you have to ask]=true,"didn't work","ha ha ha"),"not sarcasm")
    copyright © 2008 sbenj69

    Sarchasm: The gulf between the author of sarcastic wit and the person who doesn't get it.
    Reply With Quote Reply With Quote
  7. #7
    Wolffy
    Wolffy is offline
    Wolfmaster Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy's Avatar
    Join Date
    Mar 2008
    Location
    Peoria, IL
    Posts
    2,386
    Blog Entries
    5
    Real Name
    Wolff
    Rep Power
    15

    Quote Originally Posted by todd2006 View Post
    jason its giving an error

    conversion failed when converting character string to smalldatetime data type
    And hence the problem with storing dates as a character string -- you can get goofy data. My guess is that you have something is one or more of the records that doesn't look like a date. If you don't have a lot of records, you can eyeball them (sort on the date column). Otherwise, you can run some queries to try and find the offending date columns, such as:
    Code:
    Select *
from theTable
where left(std_date , 1) not in ('0','1','2','3','4','5','6','7','8','9')
or length(std_date) <> 10 -- assuming dates in mm/dd/yyyy format
    Sooner or later, you will find something that doesn't look dateish.
    Wolffy, May 3rd, 2010 04:54 PM
    Wolffy
    .-- ----- ..-. ..-. -.--
     Opinions expressed are my own and do not necessity reflect those of any sane person. Any code provided is intended to be an example and is provided AS IS. Void where prohibited by law. Not valid in California. Your mileage may vary.
    Reply With Quote Reply With Quote
  8. #8
    todd2006
    todd2006 is offline
    Barn Loyal todd2006 is an unknown quantity at this point todd2006's Avatar
    Join Date
    Mar 2008
    Posts
    889
    Rep Power
    4

    Wolfy,

    you are right looked the at the dates carefully one of them was 013/2005

    thanks to you and jason appreciate it
    todd2006, May 5th, 2010 04:55 PM
    Reply With Quote Reply With Quote
+ Reply to Thread
« Previous Thread | Next Thread »

Similar Threads

  1. VARCHAR Field Length
    By richyrich in forum MySQL
    Replies: 6
    Last Post: January 27th, 2010, 10:51 AM
  2. date problem
    By todd2006 in forum ASP Development
    Replies: 2
    Last Post: November 19th, 2009, 10:43 AM
  3. date problem
    By todd2006 in forum ASP Development
    Replies: 4
    Last Post: February 13th, 2009, 05:02 PM
  4. Conversion failed when converting the varchar error
    By guddu in forum Microsoft SQL Server
    Replies: 9
    Last Post: July 17th, 2008, 12:08 PM
  5. date problem
    By todd2006 in forum SQL Development
    Replies: 1
    Last Post: May 12th, 2008, 02:51 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


SEO by vBSEO