Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Changing Date value

Author  Topic 

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2014-02-25 : 07:44:19
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 - 2014-02-25 : 09:04:22
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

30421 Posts

Posted - 2014-02-25 : 11:37:38
[code]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;[/code]


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

maunishq
Yak Posting Veteran

71 Posts

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

It might work. I haven't tried.


!!_(M)_!!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-02-25 : 11:55:36
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 - 2014-02-26 : 12:13:09
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 - 2014-02-26 : 12:15:24
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

71 Posts

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

!_(M)_!
Go to Top of Page
   

- Advertisement -