appening last date of the month and convert to dat

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

appening last date of the month and convert to dat

Author

Topic

anjali5
Posting Yak Master

121 Posts

Posted - 2012-08-06 : 15:48:01

Hi All,

I need to parse a string in sql and convert it to date. In this e.g, I gave

a string

201102152015

2011 is year
02 is month
15 is date
20 is hour
and 15 is minute

for date, I need to calculate the last date of that month whatever the month is so for e.g if it is march it need 30 and append that in string and if it is Feburary then 28 or 29 depending on the leap year so if it is Feburary like in above e.g it will be

201102282015

In the above string, I replace 15 to 28

and then convert the above string to date.

How can I acheive this in sql

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-06 : 15:58:00

[code]
declare @s varchar(20) = '201102152015'

select dateadd(mm,1,dateval) - day(dateval) as monthenddate
from
(select convert(datetime,stuff(stuff(stuff(stuff(@s,5,0,'-'),8,0,'-'),11,0,' '),14,0,':'),121) as dateval
)t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-06 : 16:00:21

[code]DECLARE @x VARCHAR(32) = '201102152015';
SELECT CONVERT(VARCHAR(8),DATEADD(mm,DATEDIFF(mm,0,CAST(LEFT(@x,8) AS DATETIME))+1,-1),112)+STUFF(@x,1,8,'');[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-06 : 16:06:44

oh ok... I misinterpreted
dont you need it as date formatted value? then Sunitas suggestion should suffice

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anjali5
Posting Yak Master

121 Posts

Posted - 2012-08-06 : 16:15:20

Yes, I need it as date formatted value.

Thanks for all the help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-06 : 16:18:26

quote:
Originally posted by anjali5

Yes, I need it as date formatted value.

Thanks for all the help.

then you should use mine

if you just need format as your initial string use other suggestion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anjali5
Posting Yak Master

121 Posts

Posted - 2012-08-06 : 16:34:47

quote:

Originally posted by sunitabeck

DECLARE @x VARCHAR(32) = '201102152015';
SELECT CONVERT(VARCHAR(8),DATEADD(mm,DATEDIFF(mm,0,CAST(LEFT(@x,8) AS DATETIME))+1,-1),112)+STUFF(@x,1,8,'');

Sunita, can I convert your sql statement to date type.
I am trying your solution visakh, but I don't want to use from in that sql statement, can I rewrite it without sing from statement.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-06 : 16:37:53

yep you can. that was just for illustration just to give you an idea of steps involved and also not to repeat the full expression


select dateadd(mm,1,convert(datetime,stuff(stuff(stuff(stuff(@s,5,0,'-'),8,0,'-'),11,0,' '),14,0,':'),121))
-
day(convert(datetime,stuff(stuff(stuff(stuff(@s,5,0,'-'),8,0,'-'),11,0,' '),14,0,':'),121)) as dateval

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anjali5
Posting Yak Master

121 Posts

Posted - 2012-08-07 : 10:53:23

Thanks Visakh!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-07 : 11:41:49

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources