Parse out filed as date time ???

Question

Parse out filed as date time ???

jbalbo

SSCertifiable

Points: 7583
More actions
January 3, 2024 at 9:47 pm

#4356349

Hi

I have a field with a start and end date (sometimes no end date) .
How can I parse this out to have two fields start and end dates?
Data looks like
~__2019121407025301GMTOFFSET=-18000~__2019121415164202GMTOFFSET=-18000~
desired out is
Start Date 12/14/2019 7:02 AM
End Date 12/14/2019 3:16 PM

Thanks

April 6, 2024 at 3:04 pm

This was removed by the editor as SPAM

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2157 More actions · Answer 1

So you're ok with the loss of information assuming GMTOFFSET means what it appears to mean? In SQL Server if you need to preserve the same information then you'd be looking to CONVERT to DATETIME2(7) or DATETIMEOFFSET(7), yes? Is this really SQL Serve 2012? There really shouldn't be any instances anymore because it's waay out of official support

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

jbalbo SSCertifiable Points: 7583 More actions · Answer 2

Hi Steve,

Thanks for getting back. I was given this table and figured out that this field a varchar(4000) was the field with the start and end dates times. I know the first part between '~__' and GMTOFFSET=-18000 is the start and same thing for the second part as the end date

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 3

jbalbo wrote:

Hi Steve,
Thanks for getting back. I was given this table and figured out that this field a varchar(4000) was the field with the start and end dates times. I know the first part between '~__' and GMTOFFSET=-18000 is the start and same thing for the second part as the end date

Yep... Steve knows that, as well. What he's saying is that your expected returns are not the correct dates and times according to the offsets that are also provided.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101096 More actions · Answer 4

DROP TABLE IF EXISTS #data;
CREATE TABLE #data ( data varchar(4000) NULL );
INSERT INTO #data VALUES
    ('~__2019121407025301GMTOFFSET=-18000~__2019121415164202GMTOFFSET=-18000~')

SELECT data, 
    DATEADD(MINUTE, CAST(SUBSTRING(data, date1_start + 8, 2) AS int) * 60 + 
        CAST(SUBSTRING(data, date1_start + 10, 2) AS int), 
        CAST(SUBSTRING(data, date1_start, 8) AS datetime)) AS date1,
    DATEADD(MINUTE, CAST(SUBSTRING(data, date2_start + 8, 2) AS int) * 60 + 
        CAST(SUBSTRING(data, date2_start + 10, 2) AS int), 
        CAST(SUBSTRING(data, date2_start, 8) AS datetime)) AS date2
FROM #data
CROSS APPLY (
    SELECT PATINDEX('%[2][0][0-9][0-9][01][0-9][0123][0-9][01][0-9][0-5][0-9]%', data) AS date1_start
) AS ca1
CROSS APPLY (
    SELECT PATINDEX('%[2][0][0-9][0-9][01][0-9][0123][0-9][01][0-9][0-5][0-9]%', SUBSTRING(data, date1_start + 12, 4000)) + date1_start + 12 - 1 AS date2_start
) AS ca2

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

jbalbo SSCertifiable Points: 7583 More actions · Answer 5

Hi Scott,

Thank You, that's exactly what I needed.

The only issue I have now is, I looked thru the entire table(500k+ records) , since I got a error, and discovered that there can be up to 22 start and end dates in the field...

ScottPletcher SSC Guru Points: 101096 More actions · Answer 6

The code will just get the first two numbers, which might cause an "error" of bad data if the first two numbers don't have the datetimes you need. Otherwise, I wouldn't think you should get an "error".

If you need something other than the first two datetime values, are there some chars that would allow code to determine which numbers to pull?

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

jbalbo SSCertifiable Points: 7583 More actions · Answer 7

Hmmm,

The data seems to be all over the place ,but it would be the datetime between "~__ " and "GMTOFFSET=-18000"

even though it looks like there is a DPT number on some still between "~__ " and "GMTOFFSET=-18000"

Like this example

~__2019121322545101GMTOFFSET=-18000&DPT=205~__2019121408520502GMTOFFSET=-18000~__2019121409443301GMTOFFSET=-18000&DPT=242~__2019121416570802GMTOFFSET=-18000~

Where as most are

~__2019120422521801GMTOFFSET=-18000~__2019120507421602GMTOFFSET=-18000~

Then.. there are some that are NULL

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 8

You really cannot just make assumptions about the data contained in that column. You need to ask the provider of that data on what the string contains and how it needs to be parsed.

I could assume that DPT means 'DEPARTMENT' - but that is just an assumption. It could mean something else - the only way to know for sure is to ask.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

ScottPletcher SSC Guru Points: 101096 More actions · Answer 9

My original code gives a result for the new data, since I purposely wrote the original code generically:

~__2019121322545101GMTOFFSET=-18000&DPT=205~__2019121408520502GMTOFFSET=-18000~__2019121409443301GMTOFFSET=-18000&DPT=242~__2019121416570802GMTOFFSET=-18000~

2019-12-14 08:52:00.000

2019-12-14 09:44:00.000

If the first two values are not the correct ones, how specifically do I identify which values you do need? Is it the ones with "DPT" in them?? Keep in mind, we know NOTHING about your data (even less than you would, even if from another vendor).

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".