SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Changing Date value
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masond
Constraint Violating Yak Guru

447 Posts

Posted - 02/25/2014 :  07:44:19  Show Profile  Reply with Quote
Hey guys

how can i change the following date "080913" format Into 20130809

Datatype is Nvarchar(6)
Field name :[LAST-STATUS-CHG]


Appreciate your help

Robowski
Posting Yak Master

101 Posts

Posted - 02/25/2014 :  09:04:22  Show Profile  Reply with Quote
Few different ways: prob want to consider writing a function though if used heavily.

USE Tempdb;
GO
SET DATEFORMAT dmy;


IF OBJECT_ID ('#TempDate' , 'U') IS NOT NULL
DROP TABLE #TempDate;
GO

CREATE TABLE #TempDate
(
ID int IDENTITY (1,1) NOT NULL ,
DateTest Nvarchar(6) NULL ,
);
GO

INSERT INTO #TempDate (DateTest)
VALUES (N'080913');
GO

SELECT Id ,
DateTest ,
STUFF(STUFF(DateTest, 3, 0, '/'), 6, 0, '/') as ReFormat ,
CAST(DateTest as DATE) as DateCast ,
CAST(STUFF(STUFF(DateTest, 3, 0, '/'), 6, 0, '/') as date) as ReFormatCast ,
N'20' + RIGHT(Datetest, 2) + SUBSTRING(DateTest, 3, 2) + LEFT(Datetest, 2) as ReFormat ,
REPLACE(CAST(CAST(STUFF(STUFF(DateTest, 3, 0, '/'), 6, 0, '/') as date) as nvarchar(10)), '-', '') as Re ,
CONVERT(varchar(8),CAST(STUFF(STUFF(DateTest, 3, 0, '/'), 6, 0, '/') as date), 112) as DateConvert
FROM #TempDate;
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 02/25/2014 :  11:37:38  Show Profile  Visit SwePeso's Homepage  Reply with Quote
DECLARE	@Sample TABLE
	(
		Data NVARCHAR(6) NOT NULL
	);

INSERT	@Sample
	(
		Data
	)
VALUES	(
		'080913'
	);

SELECT	Data,
	CONVERT(DATETIME, STUFF(STUFF(Data, 5, 0, '/'), 3, 0, '/'), 1)
FROM	@Sample;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

maunishq
Yak Posting Veteran

Canada
71 Posts

Posted - 02/25/2014 :  11:49:52  Show Profile  Reply with Quote
SELECT FORMAT(CAST(LAST-STATUS-CHG AS Date),'yyyyMMdd') FROM table_name

It might work. I haven't tried.


!!_(M)_!!

Edited by - maunishq on 02/25/2014 11:53:46
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 02/25/2014 :  11:55:36  Show Profile  Visit SwePeso's Homepage  Reply with Quote
It will work on SQL Server 2012 and newer.
It will not work for OP. The source data is character. You will get an error message like this
Argument data type nvarchar is invalid for argument 1 of format function.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Robowski
Posting Yak Master

101 Posts

Posted - 02/26/2014 :  12:13:09  Show Profile  Reply with Quote
It wont work it will display as 20080913.

quote:
Originally posted by SwePeso

It will work on SQL Server 2012 and newer.
It will not work for OP. The source data is character. You will get an error message like this
Argument data type nvarchar is invalid for argument 1 of format function.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Go to Top of Page

Robowski
Posting Yak Master

101 Posts

Posted - 02/26/2014 :  12:15:24  Show Profile  Reply with Quote
That will display a a date 2013-08-09 00:00:00.000 not an int 20130809

quote:
Originally posted by SwePeso

DECLARE	@Sample TABLE
	(
		Data NVARCHAR(6) NOT NULL
	);

INSERT	@Sample
	(
		Data
	)
VALUES	(
		'080913'
	);

SELECT	Data,
	CONVERT(DATETIME, STUFF(STUFF(Data, 5, 0, '/'), 3, 0, '/'), 1)
FROM	@Sample;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Go to Top of Page

maunishq
Yak Posting Veteran

Canada
71 Posts

Posted - 02/26/2014 :  12:37:25  Show Profile  Reply with Quote
In an ugly way:
SELECT '20'+RIGHT([LAST-STATUS-CHG],2)+SUBSTRING([LAST-STATUS-CHG],1,4)

!_(M)_!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000