Get date in text format - 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

Get date in text format

Author

Topic

spareus
Yak Posting Veteran

52 Posts

Posted - 2010-03-08 : 08:50:49

Hi all,
I need to generate new database from quary analyser.
Database name is todays date in yy mm dd format.

I am unable to create the code.
I have the following which gives wrong results.
DECLARE @@MD VARCHAR

PRINT YEAR(GETDATE())
PRINT MONTH(GETDATE())
PRINT DAY(GETDATE())

PRINT YEAR(GETDATE()) + ' ' + MONTH(GETDATE()) + ' ' + DAY(GETDATE())
SET @@MD = YEAR(GETDATE()) + ' ' + MONTH(GETDATE()) + ' ' + DAY(GETDATE())
PRINT @@MD+'MD VALUE'

Correct result should be 10 03 08
Kindly help.

Regards,
Spareus

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-08 : 08:54:31

Where do you want to show formatted dates?

Madhivanan

Failing to plan is Planning to fail

Sachin.Nand

2937 Posts

Posted - 2010-03-08 : 09:00:04

Just convert to varchar.example convert(varchar(10),YEAR(GETDATE())) & try for all i.e month & day

PBUH

spareus
Yak Posting Veteran

52 Posts

Posted - 2010-03-10 : 00:08:55

Thanks for reply.
I am trying to create new db with todays date in SQL2000 with Qyary Analyser.
Here is my code.
DECLARE @@MYDB VARCHAR(30)
SET @@MYDB =
'['+
RIGHT('0000' + convert(varchar(4), YEAR(GetDate())), 4)+ ' '+
RIGHT('0' + convert(varchar(2), month(GetDate())), 2)+ ' '+
RIGHT('0' + convert(varchar(2), DAY(GetDate())), 2)+
']'
PRINT @@MYDB
CREATE DATABASE @@MYDB

It gives me following error.
Server: Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near '@@MYDB'.

Pl help.

Regards,
Spareus

rpc86
Posting Yak Master

200 Posts

Posted - 2010-03-10 : 00:20:48

try


convert(char(10),@yourDate,101)

spareus
Yak Posting Veteran

52 Posts

Posted - 2010-03-10 : 00:37:05

Thanks rpc86.
set @mydb=convert(char(10),getdate(),101)
PRINT @MYDB
gives result as 03/10/2010.
I need to remove "/" and need dates in yy mm dd formate to create new db.
Also I am getting same error when tried CREATE DATABASE @MYDB
Server: Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near '@MYDB'.

Regards,
Spareus

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-10 : 00:58:11

Database name must start with alphabet or '_'

try this

Declare @temp varchar(20)
set @temp= convert(varchar(20),GETDATE(),112 )
exec('create database Db_'+ @temp)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

spareus
Yak Posting Veteran

52 Posts

Posted - 2010-03-10 : 01:08:32

Thanks Senthil.
It works like a charm.
But if I use create database [2010 03 03], it also works without starting alphabat.
Any idea?
Also it would be nice if I get the db name as yy mm dd like [10 03 10]
Thanks again.

Regards,
Spareus

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-10 : 01:13:04

If you use '[]' it will works!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-10 : 01:15:56

Here yours!

Declare @temp varchar(20)
set @temp= replace(convert(varchar(20),GETDATE(),11 ),'/',' ')
exec('create database ['+ @temp +']')

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

spareus
Yak Posting Veteran

52 Posts

Posted - 2010-03-10 : 01:28:08

Thanks. This is perfact.

Regards,
Spareus

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-10 : 01:32:01

Welcome! :)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-10 : 02:06:58

quote:
Originally posted by spareus

Thanks for reply.
I am trying to create new db with todays date in SQL2000 with Qyary Analyser.
Here is my code.
DECLARE @@MYDB VARCHAR(30)
SET @@MYDB =
'['+
RIGHT('0000' + convert(varchar(4), YEAR(GetDate())), 4)+ ' '+
RIGHT('0' + convert(varchar(2), month(GetDate())), 2)+ ' '+
RIGHT('0' + convert(varchar(2), DAY(GetDate())), 2)+
']'
PRINT @@MYDB
CREATE DATABASE @@MYDB

It gives me following error.
Server: Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near '@@MYDB'.

Pl help.

Regards,
Spareus

Are you trying to create a database each day?

Madhivanan

Failing to plan is Planning to fail

spareus
Yak Posting Veteran

52 Posts

Posted - 2010-03-10 : 03:40:33

Yes. This will work as a backup on different server..
I have another server for end users where I replicate my db.
I have installed total 3 servers.
1 for me to upload daily data and do testing of new requirements. Being newbie in sql, I make
mistakes but then recover the data from backups of other server.
2 for end users
3 for backup.
This way I can greatly reduce the risk of data loss.

Regards,
Spareus

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-10 : 03:47:24

quote:
Originally posted by spareus

Yes. This will work as a backup on different server..
I have another server for end users where I replicate my db.
I have installed total 3 servers.
1 for me to upload daily data and do testing of new requirements. Being newbie in sql, I make
mistakes but then recover the data from backups of other server.
2 for end users
3 for backup.
This way I can greatly reduce the risk of data loss.

Regards,
Spareus

Then how will you club data from each db?

Madhivanan

Failing to plan is Planning to fail

spareus
Yak Posting Veteran

52 Posts

Posted - 2010-03-11 : 09:29:14

No need to club all the data coz all are simillar.
My db is having latest data which is being copied to end user db and backed up on 3rd server.
In case I make any mistake, I simply drop db and reattach from the backup server.

Regards,
Spareus

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources