Case statement

Posted on 2007-10-11
Last Modified: 2010-03-20
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


Question by:SigmundFraud
    LVL 23

    Expert Comment

    by:Ashish Patel
    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
    LVL 16

    Expert Comment

    replace(convert(varchar, getdate(), 101), '/', '')
    LVL 1

    Expert Comment

    select cast(journal_Prefix as varchar)+ convert(varchar,getdate(),112)  
    LVL 1

    Expert Comment

    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..
    LVL 50

    Accepted Solution

    ltrim(rtrim(journal_prefix)) + replace(convert(varchar(8), getdate(), 3), '/', '')

    assuming journal_prefix is a character datatype...  

    Author Comment

    Thanks guys!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now