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.

What made you think it might work?

Did you read the manual?

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.

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

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

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

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

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.

you were concatenating, you just need to nest them, as you suspected in the previous post

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