C#•14mo ago

❔ SQL query

Hey guys I have column Date which sometimes have int sometimes strings and sometimes dates In format --2022-01-26 this indicates when last time the column was updated So if I have dates --2022-04-04 --2016-04-04 And I input date 2018 I want to get 2016 2023 I want to get only 2022 as latest 2015 nothing because it wasnt updated yet having hard time with this to work properly

23 Replies

JakenVeina•14mo ago

wat oh, I see wow, I don't think that could have been worded any worse

SELECT MAX(MY_DATE)
FROM MY_TABLE
WHERE MY_DATE < @threshold

SELECT MAX(MY_DATE)
FROM MY_TABLE
WHERE MY_DATE < @threshold

sabaisabaiOP•14mo ago

What if it's gonna be string or int in my date? How to get only dates

JakenVeina•14mo ago

convert anything that's a string to a date what the hell database is this that allows mixed-type columns?

sabaisabaiOP•14mo ago

Commercial one

JakenVeina•14mo ago

which one?

Jimmacle•14mo ago

why do you have a column that can hold multiple different types of data? that's just bad design we'll need to know what database engine you're using so we have a hope of kludging together a working query like, this level of kludge

SELECT MAX(MY_DATE)
FROM MY_TABLE
WHERE COALESCE(TRY_CAST(SUBSTRING(MY_DATE, 3, 100) as date), '01-01-1900') < @threshold

SELECT MAX(MY_DATE)
FROM MY_TABLE
WHERE COALESCE(TRY_CAST(SUBSTRING(MY_DATE, 3, 100) as date), '01-01-1900') < @threshold

i didn't test that, but it would be something like that for sql server the right solution is to yell at whoever designed your database

sabaisabaiOP•14mo ago

It's designed by enova erp ecosystem it's my first week on that job SQL server First week as .net developer ever

JakenVeina•14mo ago

so, the column type is sql_variant?

sabaisabaiOP•14mo ago

I'm on my phone now, don't know whats column type but you have random numbers there or strings or dates with ~~YYYY-MM-DD

Jimmacle•14mo ago

i'm gonna guess it's just a text column

JakenVeina•14mo ago

same which is why we're going to wait for confirmation

Jimmacle•14mo ago

waiting is boring

JakenVeina•14mo ago

but more productive than working based on guesses

sabaisabaiOP•14mo ago

It's like when you add new row first row showing you date ~~YYYY-MM-DD of update and second row showing duplicated contrahent ID which is none available in row with date where it was updated they have priority numbers like 0 is newest so to get contrahentID you need to get that row with priority number and get contrahentID from row which has -1 priority number

JakenVeina•14mo ago

that's what I'm gonna do, anyway when you have access, look at the table and tell us what the actual column type is

sabaisabaiOP•14mo ago

Yeah it's probably text column that's why they adding ~~ before date? Will do sorry for that messy explanation

JakenVeina•14mo ago

then you will query against that table to determine exactly what all the possible values look like then you will write an expression to normalize all of those possibilities into whatever type of value you want presumably DATE

sabaisabaiOP•14mo ago

It's varchar

JakenVeina•14mo ago

lovely

sabaisabaiOP•14mo ago

Guys I went on gym and with fresh mind I made it 💪

Mayor McCheese•14mo ago

With an ERP you're lucky it's not an EAV system

JakenVeina•14mo ago

Accord•13mo ago

Was this issue resolved? If so, run /close - otherwise I will mark this as stale and this post will be archived until there is new activity.

Gaming

Programming

❔ SQL query