• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

Case statement

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 todays 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


1 Solution
Ashish PatelCommented:
Select Cast(DatePart(mm, Getdate()) As Varchar) + Cast(DatePart(dd, Getdate()) As VarChar) + Cast(DatePart(yy, Getdate()) As Varchar)
This will give you 111007
and cases can be written like
Select case when Batches.batch_type = 'CA' then somevalue when Batches.batch_type = 'CC' then somevalue Else some value End from batches
replace(convert(varchar, getdate(), 101), '/', '')
select cast(journal_Prefix as varchar)+ convert(varchar,getdate(),112)  
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Sorry about previous posts, did not notice the date format..

select cast(journal_Prefix as varchar)+ right(convert(varchar,getdate(),112),2)+ left(right(convert(varchar,getdate(),112),4),2)+right(left(convert(varchar,getdate(),112),4),2)

No pretty but it works..
ltrim(rtrim(journal_prefix)) + replace(convert(varchar(8), getdate(), 3), '/', '')

assuming journal_prefix is a character datatype...  
SigmundFraudAuthor Commented:
Thanks guys!

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now