January 2, 2015 at 11:37 pm
I need to convert the given data 20140927 into 27 Sept 2014. Plz help. Thanks
January 3, 2015 at 1:12 am
I'm not sure which data type you're using. Here's an example with 3 possible types.
Please, store your dates as dates and avoid using strings or numbers to store dates.
DECLARE @date date = '20140927',
@cdate char(8) = '20140927',
@idate int = '20140927'
SELECT CAST( DAY(@date) AS varchar(2)) + ' ' + STUFF( CONVERT( varchar(20), @date, 100), 5, 3, ''),
RIGHT(@cdate, 2) + ' ' + STUFF( CONVERT( varchar(20), CONVERT(date, @cdate), 100), 5, 3, ''),
RIGHT(@idate, 2) + ' ' + STUFF( CONVERT( varchar(20), CONVERT(date, CONVERT( char(8), @cdate)), 100), 5, 3, '')
January 3, 2015 at 1:28 am
--Edit: bad solution removed:-)
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 3, 2015 at 2:19 am
cheryljanicedsouza (1/2/2015)
I need to convert the given data 20140927 into 27 Sept 2014. Plz help. Thanks
Given that the originating data type is not date, the operation is twofold, first convert it to date or datetime, second part is a conversion to a character string with the right format. Consider this example
😎
USE tempdb;
SET NOCOUNT ON;
GO
DECLARE @cdate char(8) = '20140927',
@idate int = 20140927;
SELECT
/* Conversion to datetime from ISO YYYYMMDD (112) */
CONVERT(DATETIME,@cdate,112) AS CHAR_DATE
,CONVERT(DATETIME,CONVERT(VARCHAR(10),@idate,0),112) AS INT_DATE
/* Converting to the datetime to a string with an Europe DD MON YYYY format (13) */
,CONVERT(VARCHAR(12),CONVERT(DATETIME,@cdate,112),13) AS STR_CHAR_DATE
,CONVERT(VARCHAR(12),CONVERT(DATETIME,CONVERT(VARCHAR(10),@idate,0),112),13) AS STR_INT_DATE
;
Results
CHAR_DATE INT_DATE STR_CHAR_DATE STR_INT_DATE
----------------------- ----------------------- ------------- ------------
2014-09-27 00:00:00.000 2014-09-27 00:00:00.000 27 Sep 2014 27 Sep 2014
January 3, 2015 at 2:23 am
Luis Cazares (1/3/2015)
DECLARE @date date = '20140927'
Be careful with the implicit conversion as the results will be depending on the system settings, no guarantee that it will be interpreted as ISO YYYYMMDD.
😎
January 3, 2015 at 3:08 am
Eirikur Eiriksson (1/3/2015)
Luis Cazares (1/3/2015)
DECLARE @date date = '20140927'
Be careful with the implicit conversion as the results will be depending on the system settings, no guarantee that it will be interpreted as ISO YYYYMMDD.
😎
Eirikur, your solution produces 'Sep' rather than 'Sept' for the month. (Which is what my 'bad' solution did too!)
--Edit: As far as I know, a string in the format 'YYYYMMDD' is always interpreted correctly by SQL Server, regardless of settings.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 3, 2015 at 3:45 am
My whole column needs to be converted into that format, not the specific field
January 3, 2015 at 3:51 am
Phil Parkin (1/3/2015)
Eirikur Eiriksson (1/3/2015)
Luis Cazares (1/3/2015)
DECLARE @date date = '20140927'
Be careful with the implicit conversion as the results will be depending on the system settings, no guarantee that it will be interpreted as ISO YYYYMMDD.
😎
Eirikur, your solution produces 'Sep' rather than 'Sept' for the month. (Which is what my 'bad' solution did too!)
--Edit: As far as I know, a string in the format 'YYYYMMDD' is always interpreted correctly by SQL Server, regardless of settings.
Good catch:pinch:, didn't notice the non-standard abbreviation of the month, neither the CONVERT nor FORMAT facilitates for this format hence one has to substring the month's full name.
😎
USE tempdb;
SET NOCOUNT ON;
GO
DECLARE @cdate char(8) = '20140927',
@idate int = 20140927;
SELECT
/* Conversion to datetime from ISO YYYYMMDD (112) */
CONVERT(DATETIME,@cdate,112) AS CHAR_DATE
,CONVERT(DATETIME,CONVERT(VARCHAR(10),@idate,0),112) AS INT_DATE
/* Converting to the datetime to a string with an Europe DD MON YYYY format (13) */
,CONVERT(VARCHAR(12),CONVERT(DATETIME,@cdate,112),13) AS STR_CHAR_DATE
,CONVERT(VARCHAR(12),CONVERT(DATETIME,CONVERT(VARCHAR(10),@idate,0),112),13) AS STR_INT_DATE
/* Formatting the results with a non-standard abbreviation of the month name */
,CONCAT(DATEPART(DAY,CONVERT(DATETIME,@cdate,112))
,CHAR(32)
,SUBSTRING(DATENAME(MONTH,CONVERT(DATETIME,@cdate,112)),1,4)
,CHAR(32)
,YEAR(CONVERT(DATETIME,@cdate,112))) AS NST_CHAR_DATE
,CONCAT(DATEPART(DAY,CONVERT(DATETIME,CONVERT(VARCHAR(10),@idate,0),112))
,CHAR(32)
,SUBSTRING(DATENAME(MONTH,CONVERT(DATETIME,CONVERT(VARCHAR(10),@idate,0),112)),1,4)
,CHAR(32)
,YEAR(CONVERT(DATETIME,CONVERT(VARCHAR(10),@idate,0),112))) AS NST_INT_DATE
;
Results
CHAR_DATE INT_DATE STR_CHAR_DATE STR_INT_DATE NST_CHAR_DATE NST_INT_DATE
----------------------- ----------------------- ------------- ------------ --------------- -------------
2014-09-27 00:00:00.000 2014-09-27 00:00:00.000 27 Sep 2014 27 Sep 2014 27 Sept 2014 27 Sept 2014
Edit: the quote bug.
January 3, 2015 at 4:24 am
cheryljanicedsouza (1/3/2015)
My whole column needs to be converted into that format, not the specific field
Any solution proposed here will, of course, need to modified to run against your data, but the techniques proposed will be the same.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 3, 2015 at 9:15 am
cheryljanicedsouza (1/3/2015)
My whole column needs to be converted into that format, not the specific field
Careful, now. It's pretty easy to return formatted data but you should never store dates as formatted data. You'll hate yourself in the future when you need to do some date math because everything you do will need to be converted to a date/time datatype to do it.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 3, 2015 at 3:27 pm
As other people have mentioned, convert it to a date or datetime value first, and then convert it back to a string using the CONVERT, date, and string functions.
Here are a couple of ways to do it, depending on if you want a leading zero on the day or not.
The code posted below assumes that the date to be converted will always be a valid date string in format YYYYMMDD. If this is not the case, then different code will be required.
My assumption is that you only want a 4 character month abbreviation for June, July, and September, and will want 3 characters for all other months. If that is not the case, modify the CASE expression for AbbrLen as needed.
;with test_data_cte as (
select
a1.*, Dt = convert(date,a1.CharDate),
AbbrLen =
case when month(a1.CharDate) in (6,7,9)
then 4 else 3 end
from
(
selectCharDate = '20140107' union all
selectCharDate = '20140207' union all
selectCharDate = '20140307' union all
selectCharDate = '20140407' union all
selectCharDate = '20140507' union all
selectCharDate = '20140607' union all
selectCharDate = '20140707' union all
selectCharDate = '20140807' union all
selectCharDate = '20140907' union all
selectCharDate = '20141007' union all
selectCharDate = '20141107' union all
selectCharDate = '20141207'
) a1
)
select
a.CharDate, a.Dt,
NewCharDate1 =
convert(varchar(12),
datename(day,a.DT)+' '+
left(datename(month,a.Dt),a.AbbrLen)+' '+
datename(year,Dt)),
NewCharDate2 =
convert(varchar(12),
stuff(convert(varchar,a.Dt,106)
,4,3,left(datename(month,a.Dt),a.AbbrLen)))
from
test_data_cte a
Results:
CharDate Dt NewCharDate1 NewCharDate2
-------- ---------- ------------ ------------
20140107 2014-01-07 7 Jan 2014 07 Jan 2014
20140207 2014-02-07 7 Feb 2014 07 Feb 2014
20140307 2014-03-07 7 Mar 2014 07 Mar 2014
20140407 2014-04-07 7 Apr 2014 07 Apr 2014
20140507 2014-05-07 7 May 2014 07 May 2014
20140607 2014-06-07 7 June 2014 07 June 2014
20140707 2014-07-07 7 July 2014 07 July 2014
20140807 2014-08-07 7 Aug 2014 07 Aug 2014
20140907 2014-09-07 7 Sept 2014 07 Sept 2014
20141007 2014-10-07 7 Oct 2014 07 Oct 2014
20141107 2014-11-07 7 Nov 2014 07 Nov 2014
20141207 2014-12-07 7 Dec 2014 07 Dec 2014
January 5, 2015 at 4:23 pm
Phil Parkin (1/3/2015)
Eirikur Eiriksson (1/3/2015)
Luis Cazares (1/3/2015)
DECLARE @date date = '20140927'
Be careful with the implicit conversion as the results will be depending on the system settings, no guarantee that it will be interpreted as ISO YYYYMMDD.
😎
Eirikur, your solution produces 'Sep' rather than 'Sept' for the month. (Which is what my 'bad' solution did too!)
--Edit: As far as I know, a string in the format 'YYYYMMDD' is always interpreted correctly by SQL Server, regardless of settings.
From BOL:
ISO 8601
YYYY-MM-DDThh:mm:ss[.mmm]
YYYYMMDD[ hh:mm:ss[.mmm]]
The advantage in using the ISO 8601 format is that it is an international standard with unambiguous specification. Also, this format is not affected by the SET DATEFORMAT or SET LANGUAGE setting.
That's why I love that format. 😉
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply