Help with case statement - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Help with case statement

Author

Topic

Sini
Starting Member

18 Posts

Posted - 2007-10-11 : 11:48:24

Hello people

I need some help with the following in sql.

Batches.journal_number to be updated with the journal prefix as per the below table, followed by today’s date. I can get the prefix and use getdate() to generate the date but am struggling in converting the format from 11/10/07 to 111007. The end result should be 51111007 for CA batch_type for today.

Example of table:

Batches.batch_type | batch_type_desc | Journal Prefix
CA | Cash | 51
CC | Credit Cards | 52

Please can you help? I need case select (or similar) for each of the batch types, ideally to run as a single script.

Many thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-11 : 11:55:28

[code]Update Batches
set journal_number = Journal_Prefix + replace(convert(varchar(8), getdate(), 3), '/', '')
from Batches[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Sini
Starting Member

18 Posts

Posted - 2007-10-11 : 12:05:28

Thanks for the reply.

I thought about replacing the slashe with blanks but then i thought it may leave gaps.

I am a victim of your sig but i will learn :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources