Manipulate Date Output

Question

Post reply

Manipulate Date Output

monitor89

Grasshopper

Points: 12
More actions
March 12, 2010 at 4:52 pm

#217820

I have YYYYMMDD stored in dob column in table h.
The line below gives me MMDDYYYY
substr(h.dob,5,2) || right(h.dob,2) || left(h.dob,4) as Birth_Date,
What should I change/add to insert slashes into the Birth_Date?
MM/DD/YYYY
thanks

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Please see BOL (BooksOnline, the SQL Server help system usually installed toegther with SQL Server) section "CONVERT".

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 2

monitor89 (3/12/2010)
I have YYYYMMDD stored in dob column in table h.
The line below gives me MMDDYYYY
substr(h.dob,5,2) || right(h.dob,2) || left(h.dob,4) as Birth_Date,
What should I change/add to insert slashes into the Birth_Date?
MM/DD/YYYY
thanks

Ummmm.... SQL Server is quite a bit different than Oracle... you might want to try an Oracle forum for your question. I believe that you'll need to lookup To_Date and To_Char in order to pull this off correctly using date formatting.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

monitor89 Grasshopper Points: 12 More actions · Answer 3

Got it figured out...

Original

substr(h.dob,5,2) || right(h.dob,2) || left(h.dob,4) as Birth_Date,

New

substr(h.dob,5,2) || '/' || right(h.dob,2) || '/' || left(h.dob,4) as Birth_Date,

...pretty easy actually.

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 4

monitor89 (3/15/2010)
Got it figured out...
Original
substr(h.dob,5,2) || right(h.dob,2) || left(h.dob,4) as Birth_Date,
New
substr(h.dob,5,2) || '/' || right(h.dob,2) || '/' || left(h.dob,4) as Birth_Date,
...pretty easy actually.

How about ...

TO_CHAR(TO_DATE(h.dob,'yyyymmdd'), 'mm/dd/yyyy') AS Birth_Date

😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)