?
Solved

Convert date format in MS SQL

Posted on 2014-02-05
5
Medium Priority
?
729 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 1000 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 70

Accepted Solution

by:
Scott Pletcher earned 1000 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

719 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