Solved

Convert date format in MS SQL

Posted on 2014-02-05
5
675 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to install/upgrade the Blitz responder kit 8 42
sql help 8 55
Common Records between Sub Queries 4 25
SQL Server Insert where not exists 24 41
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 we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

785 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