March 13, 2012 at 6:39 am
I need the Year & the Month of a Date Field Formatted as 'YYYYMM'.
Any help would be greatly appreciated.
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 13, 2012 at 6:50 am
I'm using the DatePart Function but I need to include a CAST.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 13, 2012 at 6:54 am
--select cast(datepart(month,getdate()) as varchar(2)) + cast(datepart(day,getdate()) as varchar(2)) + cast(datepart(year,getdate()) as varchar(4))
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 13, 2012 at 6:55 am
March 13, 2012 at 6:55 am
SELECT
CONVERT(CHAR(4), YEAR(GetDate()))+
CASE WHEN LEN(MONTH(GetDate())) = 1
THEN '0'+CONVERT(CHAR(1),MONTH(GetDate()))
ELSE CONVERT(CHAR(2),MONTH(GetDate()))
END
March 13, 2012 at 7:30 am
Thanks for the code.
When I tried to add the day for another Column I get a syntax error.
CONVERT(CHAR(4), YEAR(EffectiveDate))+
CASE
WHEN LEN(MONTH(EffectiveDate)) = 1
THEN '0'+CONVERT(CHAR(1),MONTH(EffectiveDate))
ELSE CONVERT(CHAR(2),MONTH(EffectiveDate))
CASE
WHEN LEN(DAY(EffectiveDate)) = 1
THEN '0'+CONVERT(CHAR(1),DAY(EffectiveDate))
ELSE CONVERT(CHAR(2),DAY(EffectiveDate))
END AS CEFEFFDT,
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 13, 2012 at 7:33 am
you need an end after the first case
CONVERT(CHAR(4), YEAR(EffectiveDate))+
CASE
WHEN LEN(MONTH(EffectiveDate)) = 1
THEN '0'+CONVERT(CHAR(1),MONTH(EffectiveDate))
ELSE CONVERT(CHAR(2),MONTH(EffectiveDate))
END,
CASE
WHEN LEN(DAY(EffectiveDate)) = 1
THEN '0'+CONVERT(CHAR(1),DAY(EffectiveDate))
ELSE CONVERT(CHAR(2),DAY(EffectiveDate))
END AS CEFEFFDT
March 13, 2012 at 7:34 am
That was dumb, thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 13, 2012 at 7:39 am
Welsh Corgi (3/13/2012)
I need the Year & the Month of a Date Field Formatted as 'YYYYMM'.Any help would be greatly appreciated.
Thanks.
Your question is quite obscure or incorrect (whatever you like)!
"Date Field Formatted as 'YYYYMM'" - In SQL Server you cannot have Date field formatted!
You can have column of datetime (or other date) type, which will contain full date and can be converted to string (eg. varchar) formatted as required for output in a query.
OR
You can have varchar/char column which contains the value representing the date in any format including 'YYYYMM'
If you want to get YEAR and MONTH out of datetime value, you can use YEAR() and MONTH() function, if you need year and month out of string, in your case you can do:
SELECT YEAR([YourYYYYMMstring] + '01' ), MONTH([YourYYYYMMstring] + '01' )
the above works on SQL Server recognition of ISO YYYYMMDD format, so string like that will be implicitly converted to Datetime when attempted to be used as datetime (eg. inside of datetime functions)...
March 13, 2012 at 7:42 am
And if you want convert DateTime to string and format it as YYYYMM, you can simply do this:
select LEFT(CONVERT(VARCHAR,GETDATE(),112),6)
- 112 - is code for ISO format
Edited: I can see that Calvo, already suggested similar to the above in his previous post...
March 13, 2012 at 8:47 am
I agree that I did not state my question very well.
The format of the column the data was being inserted to was the format that I specified and no it is not a DateTime Column.
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 13, 2012 at 9:11 am
A problem that I'm running into on this is that it creates a second colum once it encounters the END in the CASE Statement.
I need one column of output.
CONVERT(CHAR(4), YEAR(EffectiveDate))+
CASE
WHEN LEN(MONTH(EffectiveDate)) = 1
THEN '0'+CONVERT(CHAR(1),MONTH(EffectiveDate))
ELSE CONVERT(CHAR(2),MONTH(EffectiveDate))
END,
CASE
WHEN LEN(DAY(EffectiveDate)) = 1
THEN '0'+CONVERT(CHAR(1),DAY(EffectiveDate))
ELSE CONVERT(CHAR(2),DAY(EffectiveDate))
END AS CEFEFFDT,
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 13, 2012 at 9:15 am
replace the , for a + to concatinate the strings at the end of the first case
March 13, 2012 at 9:16 am
Try this:
cast(year(EffectiveDate) as char(4)) + right('0' + cast(month(EffectiveDate) as varchar(3)), 2) + right('0' + cast(day(EffectiveDate) as varchar(3)), 2)
March 13, 2012 at 9:27 am
Can someone explain why everyone suggesting huge concatenation logic when date in YYYYMMDD format can be produced as a simple convert with option 112 for ISO?
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply