question about Declare and convert date

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

question about Declare and convert date

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-10-04 : 22:49:34

This code, what does the 120 + -01 120 signify?
what does the convert (Varchar 7 ) accomkplish?

DECLARE @NextMonthStart DATETIME = CONVERT(DATETIME, CONVERT(VARCHAR(7), DATEADD(MONTH, 1, GETDATE()), 120) + '-01', 120)

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-04 : 23:11:32

Hi,

120 is a datetime format and gives you date in yyyy-mm-dd hh:mi:ss(24h) format
Varchar(7) is returning only 7 character max

Your example will work like this:
Step 1: DATEADD(MONTH, 1, GETDATE()), 120)
-- This add one month to current datetime.
--The output will be 2010-11-05 08:32:51.450

Step 2: CONVERT(VARCHAR(7), '2010-11-05 08:32:51.450', 120)
--Returns 2010-11

Step 3: Select CONVERT(DATETIME, '2010-11'+ '-01', 120)
--Returns 2010-11-01 00:00:00.000

It looks like it is returning first date of next month

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-10-05 : 06:12:11

quote:
Originally posted by AdamWest

This code, what does the 120 + -01 120 signify?
what does the convert (Varchar 7 ) accomkplish?

DECLARE @NextMonthStart DATETIME = CONVERT(DATETIME, CONVERT(VARCHAR(7), DATEADD(MONTH, 1, GETDATE()), 120) + '-01', 120)

Read about CONVERT function in SQL Server help file

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources