GETDATE() - SQL Server Forums

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

GETDATE()

Author

Topic

ddt
Starting Member

10 Posts

Posted - 2010-05-04 : 03:37:04

how to get current month with GETDATE() in sql 2008 and 2005?

thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-04 : 03:38:39

You can use the MONTH function: SELECT MONTH(GETDATE())

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-04 : 03:47:35

for month name use

DATENAME(mm,GETDATE())

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-04 : 03:52:09

I prefer to handle the data in number format and then get the name from the presentation layer.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ddt
Starting Member

10 Posts

Posted - 2010-05-04 : 04:13:39

Thanks...

I am using it in this script

DECLARE @name VARCHAR(100) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = '\\server\sqlbackup'

SELECT @fileDate = CONVERT(VARCHAR(20),DATENAME(mm,GETDATE()))

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name
END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-04 : 11:52:53

I've never seen anyone use DATENAME in a backup file. A better backup file name is to use the timestamp. If you want a tried and tested custom backup script, you should check out this: http://weblogs.sqlteam.com/tarad/archive/2009/12/29/Backup-SQL-Server-2005-and-2008-DatabasesAgain.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-07 : 04:22:28

quote:
Originally posted by ddt

Thanks...

I am using it in this script

DECLARE @name VARCHAR(100) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = '\\server\sqlbackup'

SELECT @fileDate = CONVERT(VARCHAR(20),DATENAME(mm,GETDATE()))

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name
END

This can tbe simplified to

Declare @sql varchar(max)
set @sql=''
select @sql=@sql+
	'
	Backup database '+name+' to disk=''\\server\sqlbackup'+name+'_'+convert(varchar(8),getdate(),112)+'.bak''' 
	from master..sysdatabases
	where name NOT IN ('master','model','msdb','tempdb') 
--print @sql
EXEC(@sql)

Madhivanan

Failing to plan is Planning to fail

ddt
Starting Member

10 Posts

Posted - 2010-05-14 : 02:21:13

thanks for your responses... still lot to learn

tara, thanks for the script.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources