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 20130809Datatype 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;GOSET DATEFORMAT dmy;IF OBJECT_ID ('#TempDate' , 'U') IS NOT NULL DROP TABLE #TempDate;GOCREATE TABLE #TempDate ( ID int IDENTITY (1,1) NOT NULL , DateTest Nvarchar(6) NULL , );GOINSERT INTO #TempDate (DateTest)VALUES (N'080913');GOSELECT 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 DateConvertFROM #TempDate; |
|
|
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 |
|
|
maunishq
Yak Posting Veteran
71 Posts |
Posted - 2014-02-25 : 11:49:52
|
SELECT FORMAT(CAST(LAST-STATUS-CHG AS Date),'yyyyMMdd') FROM table_nameIt might work. I haven't tried.!!_(M)_!! |
|
|
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 thisArgument data type nvarchar is invalid for argument 1 of format function. Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
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 thisArgument data type nvarchar is invalid for argument 1 of format function. Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
|
|
|
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 20130809quote: 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
|
|
|
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)_! |
|
|
|
|
|