Improve company productivity with a Business Account.Sign Up

x
?
Solved

Convert date format in MS SQL

Posted on 2014-02-05
5
Medium Priority
?
770 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 41

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 41

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

589 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