May 7, 2014 at 1:27 pm
Hi,
I need to get extract the year and month from the currentdate and concatenate with 01
YYYYMM01
e.g. 20140501
I have tried various ways :
left
datepart
cast
convert
But just not happening, please can anyone help...
Thanks
May 7, 2014 at 1:34 pm
SELECT YEAR(GETDATE())*10000 + MONTH(GETDATE())*100 + DAY(GETDATE())
May 7, 2014 at 1:38 pm
Here is another way.
select CONVERT(varchar, dateadd(mm, datediff(mm, 0, GETDATE()), 0), 112)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 7, 2014 at 1:38 pm
wow thanks for the quick reply it worked, i changed the day to
+ '01' as need it to represent 01 always.
Intrigued as to why need to *1000 for year and *100 month?
Will this still if work if month = 10, 11 or 12?
May 7, 2014 at 1:41 pm
SQL Guy 1 (5/7/2014)
SELECT YEAR(GETDATE())*10000 + MONTH(GETDATE())*100 + DAY(GETDATE())
You could just use convert on GETDATE() to accomplish this. 😉
select CONVERT(varchar, getdate(), 112)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 7, 2014 at 1:45 pm
hi sean,
i always need the day to be 01
May 7, 2014 at 1:50 pm
select CONVERT(varchar(6), getdate(), 112) +'01'
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 7, 2014 at 1:53 pm
Informer30 (5/7/2014)
hi sean,i always need the day to be 01
Yeah that is exactly what my first post does. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 7, 2014 at 2:07 pm
ok thanks guys all great replies....
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply