#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    18
    Rep Power
    0

    manual query date formating


    I am trying to setup some software that allows me to define expressions manually.

    Using the SQL Enterprise manager on a certain table, I am trying to run a query but I am stuck.

    There is a field in TableA that is called expDate which is set as 01/12/2004 as the value.

    What I am trying to do is convert the output to say 12-Jan-2004 using a built in function, but I can't seem to find the right function.

    Does anybody know the function to do this? Here is what I was thinking might work (but didn't):

    SELECT DATE_FORMAT('dd-mmm-yyyy',expDate) AS expire from TableA WHERE (lastName = 'me')

    If I leave out the DATE_FORMAT and just put SELECT expDate it returns one row named expire with the value of 01/12/2004

    Any Ideas? Thanks.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    Here is what I was thinking might work
    What made you think it might work?

    Did you read the manual?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    18
    Rep Power
    0
    I'm a linux person who normally uses mySQL for stuff. I'm being forced to try and make this work. It's hard finding an answer on the internet or even in a manual when the wording can be considred different from what I am used to.

    If it was a mySQL based server, I would have no problem finding the answer:

    date_format('field','formatting') as newfield

    so I figured I give that at try and it failed. I was just asking a legit question and trying to find an answer. Looking at that link really hasn't told me much to the degree what I was looking for.

    I was hoping that somebody here has run into this or knows how to do it and would kindly share an example, that is all I was hoping for.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    18
    Rep Power
    0
    Ok. I came up with this, but does anybody know of an easier way to write this to acheive the same effect?:

    DATENAME([day],expDate)+'-'+SUBSTRING(DATENAME([month],expDate),0,4)+'-'+DATENAME([year],expDate) as expire

    Returns (example): 14-Jan-2004 from the actual database entry 01/14/2004.

    And shammat thanks for the link , but it still is new water for me from what I am used to
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,145
    Rep Power
    4274
    easier? that's somewhat subjective

    i would use CONVERT style 113 into a CHAR(11) field and then REPLACE to change the spaces to dashes

    select replace(convert(char(11),expDate,113),' ','-')

    if you're using sql server, you should have Books OnLine (BOL) on one of your drives somewhere

    if not, holler, i'll give you the link where you can download the whole thing (it's 38megs or something)

    or just use BOL on the web like i do
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    18
    Rep Power
    0
    Thank you Much easier and efficient.

    I'll have to check out those BOL and see if I can solve the next problem

    Do you know if it's possbile to nest multiple REPLACE statements to parse a field more than once?

    Ex: M,0,23|Tu,0,23|W,0,23

    Into:
    M 0-23
    Tu 0-23
    W 0-23

    And so on.. (Turning the | into a newline break and then changing the first ',' into a space and then the next into a - )....

    Thanks for your help with the date item.. thats a big timesaver
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,145
    Rep Power
    4274
    Originally posted by ecads
    Do you know if it's possbile to nest multiple REPLACE statements to parse a field more than once?
    yes it is, and at this point you should be testing stuff like that yourself, it's certainly going to get you answers faster than any forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    18
    Rep Power
    0
    Thats what I've been doing and came up with that monsterous DATENAME one. I'll find it eventually, just sucks when your boss is saying he needs it in a short amount of time

    I tried doing something like REPLACE(fieldName,'aa','bb')+REPLACE(fieldName,'cc','dd') as newField

    But that just seemed to merge it into a bigger mess heh. Thanks for your help, i'll poke around more and see what I can come up with.
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,145
    Rep Power
    4274
    you were concatenating, you just need to nest them, as you suspected in the previous post

    replace(replace(fieldName,'aa','bb'),'cc','dd'))


IMN logo majestic logo threadwatch logo seochat tools logo