DynDeveloper.com

This is a little more complicate than you might think. Simply using DATEDIFF won't work correctly, because it only subtracts the 'year' portion of the date. It doesn't take into account if the month/day is before or after the current date. 

I've come across a formula that works, and it's not too complicated. See below. 

Ever have one of those moments where you 'hear the angels sing'? A 'man, that's so cool' moment? I just had one. 

I'm editing an ACA query just now (has to do with year end reporting). 3 days ago I wrote a formula, ran it a few times, then the client told me it wasn't needed so I removed it. Today, I need it <forehead slap>. And it was a paid to research and code, I needed a person's age on Jan 1 for the report. 

Enter Red Gate SQL Prompt:

I have a SQL Table that I need to update with a BATCHID based upon data from the table.

The Table has a Posting Date Column with Dates formatted (example) 2024-01-30

I need to update the Batchid based upon a string plus the date (formatted without hyphens)

Something like Update Batchid = CONCAT('YAYPAY_'+PostingDate)

So if posting date in table is 2021-01-30 then BatchID (has to be 15 chars max) should be: 'YayPay_20240130'

How do I strip out the hyphens and format the date so it fits 15 characters? 

Thanks in advance

Okay - I'm trying to grab the first five letters of a customer name field in GP. In some cases, the Customer name starts with numbers which I need to strip out. 

Example - here the data:

I'm trying to use PATINDEX and LEFT to get the first five letters -

Here's my feeble attempt:

select LEFT( PATINDEX('%[A-Z]%', CUSTNAME), 5) as CustomerName from RM00101

This returns - 

Obviously, it's just counting where the first letter begins not returning anything. 

I originally tried substring - then settled on PATINDEX.

Any help would be greatly appreciated - thanks