• 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

 

0
SigmundFraud
Asked:
SigmundFraud
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
0
 
SQL_SERVER_DBACommented:
replace(convert(varchar, getdate(), 101), '/', '')
0
 
a016691Commented:
select cast(journal_Prefix as varchar)+ convert(varchar,getdate(),112)  
0
 [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

 
a016691Commented:
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..
0
 
LowfatspreadCommented:
ltrim(rtrim(journal_prefix)) + replace(convert(varchar(8), getdate(), 3), '/', '')

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

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