C#•3y 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

JakenVeina•10/27/23, 6:37 PM

wat

JakenVeina•10/27/23, 6:38 PM

oh, I see

JakenVeina•10/27/23, 6:38 PM

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

JakenVeina•10/27/23, 6:39 PM

sabaisabaiOP•10/27/23, 6:46 PM

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

JakenVeina•10/27/23, 6:49 PM

convert anything that's a string to a date

JakenVeina•10/27/23, 6:49 PM

what the hell database is this that allows mixed-type columns?

sabaisabaiOP•10/27/23, 6:53 PM

Commercial one

JakenVeina•10/27/23, 6:55 PM

which one?

Jimmacle•10/27/23, 7:23 PM

why do you have a column that can hold multiple different types of data?

Jimmacle•10/27/23, 7:24 PM

that's just bad design

Jimmacle•10/27/23, 7:25 PM

we'll need to know what database engine you're using so we have a hope of kludging together a working query

Jimmacle•10/27/23, 7:28 PM

like, this level of kludge

Jimmacle•10/27/23, 7:28 PM

i didn't test that, but it would be something like that for sql server

Jimmacle•10/27/23, 7:29 PM

the right solution is to yell at whoever designed your database

sabaisabaiOP•10/27/23, 9:26 PM

It's designed by enova erp ecosystem it's my first week on that job

sabaisabaiOP•10/27/23, 9:27 PM

SQL server

sabaisabaiOP•10/27/23, 9:27 PM

First week as .net developer ever

JakenVeina•10/27/23, 9:31 PM

so, the column type is ?

sabaisabaiOP•10/27/23, 9:35 PM

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•10/27/23, 9:39 PM

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

JakenVeina•10/27/23, 9:40 PM

same

JakenVeina•10/27/23, 9:40 PM

which is why we're going to wait for confirmation

Jimmacle•10/27/23, 9:40 PM

waiting is boring

JakenVeina•10/27/23, 9:40 PM

but more productive than working based on guesses

sabaisabaiOP•10/27/23, 9:41 PM

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

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

JakenVeina•10/27/23, 9:41 PM

that's what I'm gonna do, anyway

JakenVeina•10/27/23, 9:41 PM

when you have access, look at the table and tell us what the actual column type is

sabaisabaiOP•10/27/23, 9:41 PM

Yeah it's probably text column that's why they adding ~~ before date?

sabaisabaiOP•10/27/23, 9:41 PM

Will do sorry for that messy explanation

JakenVeina•10/27/23, 9:41 PM

then you will query against that table to determine exactly what all the possible values look like

JakenVeina•10/27/23, 9:42 PM

then you will write an expression to normalize all of those possibilities into whatever type of value you want

JakenVeina•10/27/23, 9:42 PM

presumably

sabaisabaiOP•10/27/23, 9:52 PM

It's varchar

JakenVeina•10/27/23, 10:03 PM

lovely

sabaisabaiOP•10/27/23, 10:17 PM

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

Mayor McCheese•10/27/23, 10:37 PM

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

JakenVeina•10/27/23, 11:00 PM

Accord•10/28/23, 11:07 PM

Was this issue resolved? If so, run

/close

/close

otherwise I will mark this as stale and this post will be archived until there is new activity.