Solved

Convert date format in MS SQL

Posted on 2014-02-05
5
709 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 40

Expert Comment

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

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
 
LVL 1

Author Closing Comment

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

Featured Post

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Copy Database Wizard 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 this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

635 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