Solved

Convert date format in MS SQL

Posted on 2014-02-05
5
685 Views
Last Modified: 2014-04-10
Experts,
I have a date stored as a string stored in a SQL table as 01312015  (DDMMYYYY).
I need to select it twice.
once as  YYYYMMDD  (20150131)
once as MONDDYY  (JAN3115)

Trying SELECT CONVERT(VARCHAR(........  
but many errors

Please help
0
Comment
Question by:JDCam
  • 2
  • 2
5 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39836920
DDMMYYYY or MMDDYYYY?
0
 

Author Comment

by:JDCam
ID: 39836931
Stored value is DDMMYYYY   31012015
... sorry example was backwards
0
 
LVL 40

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 250 total points
ID: 39836946
declare @D varchar(8)
set @D = '31012015'
select
--just as an example, not need
cast(right(@D, 4)+substring(@D, 3, 2)+left(@D, 2) as datetime),
--first
       replace(convert(VARCHAR, cast(right(@D, 4)+substring(@D, 3, 2)+left(@D, 2) as datetime),111),'/','')  t1,
--second
       replace(replace(convert(VARCHAR, cast(right(@D, 4)+substring(@D, 3, 2)+left(@D, 2) as datetime),7),' ',''),',','')  t2

replace @d with your column


select         replace(convert(VARCHAR, cast(right(<col>, 4)+substring(<col>, 3, 2)+left(<col>, 2) as datetime),111),'/','')  t1
from <table>
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 250 total points
ID: 39839464
I like to use CROSS APPLY to keep from having to repeat data manipulation code.  It  basically gives a name to a computed/calculated column, which, to me, makes code much easier to read and to maintain:


-- against hard-coded sample data

SELECT
    yyyy + mm + dd AS first_result,
    UPPER(CONVERT(char(3), CAST(yyyy+mm+dd AS datetime), 0)) + dd + RIGHT(yyyy, 2)
FROM (
    SELECT '31012015' AS ddmmyyyy
) AS sample_table_data
-- cross apply used to add names to computed/calculated columns
CROSS APPLY (
    SELECT LEFT(ddmmyyyy, 2) AS dd, SUBSTRING(ddmmyyyy, 3, 2) AS mm, RIGHT(ddmmyyyy, 4) AS yyyy
) AS cross_apply_1



-- against your actual table:

SELECT
    yyyy + mm + dd AS first_result,
    UPPER(CONVERT(char(3), CAST(yyyy+mm+dd AS datetime), 0)) + dd + RIGHT(yyyy, 2)
FROM dbo.your_table_name
-- cross apply used to add names to computed/calculated columns
CROSS APPLY (
    SELECT LEFT(ddmmyyyy, 2) AS dd, SUBSTRING(ddmmyyyy, 3, 2) AS mm, RIGHT(ddmmyyyy, 4) AS yyyy
) AS cross_apply_1
0
 

Author Closing Comment

by:JDCam
ID: 39992028
Thanks.. points shared
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question