SQL statement --- HELP

Discussion in 'OT Technology' started by michelin man, Feb 23, 2004.

  1. michelin man

    michelin man IDB Construction

    Joined:
    Jul 22, 2001
    Messages:
    11,658
    Likes Received:
    0
    Location:
    Austin
    this string is from an access db
    DateAdd("d",-(Format([date_effective],"d")-1),[date_effective]) AS SvcMonth

    this is the error i get when i try it in sql (query analyzer):
    'Format' is not a recognized function name.

    what do i need to change?
     
  2. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    If the SQL function is running in Access, it should work. If you run it outside of access, well, it won't. Access used Jet SQL for running its queries which allows user and built in VB functions in the query process. Having said that, I would suggest you use dateserial or datepart instead for what you are trying to do. I think you're asking the computer to come up with the previous month:

    1 month ago exactly:

    dateserial(year(date_effective),month(date_effective)-1,day(date_effective))

    Last day of previous month:

    dateserial(year(date_effective),month(date_effective),0)
     
  3. carlin

    carlin Guest

  4. michelin man

    michelin man IDB Construction

    Joined:
    Jul 22, 2001
    Messages:
    11,658
    Likes Received:
    0
    Location:
    Austin
    this worked....

    thanks for the help

    DATEADD(d, - (DATEPART(d, DATE_PAID) - 1), DATE_PAID) AS PdMonth
     
  5. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Date serial didn't work? It's much cleaner IMHO.
     
  6. michelin man

    michelin man IDB Construction

    Joined:
    Jul 22, 2001
    Messages:
    11,658
    Likes Received:
    0
    Location:
    Austin
    date serial would not work...

    in enterprise manager:

    'dateserial' is not a recognized function name
     
  7. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Well there you go - perhaps if I didn't work in access all the time I might know that ;)
     
  8. michelin man

    michelin man IDB Construction

    Joined:
    Jul 22, 2001
    Messages:
    11,658
    Likes Received:
    0
    Location:
    Austin
    either way thanks
     
  9. lol

    unfortunately CAST and CONVERT hassles dominate the big league MS DBs :(
     

Share This Page