Convert character and then to Datetime

Get Help
1

Hi guys,

I am trying to convert a datakey to a character first and then to Datetime format.

This is the query:
SELECT
CONVERT(DATETIME,CONVERT(nvarchar(8), “Dim_Date__via__BookedDateKey”.“DateKey”)) AS “DateString”
LEFT JOIN “dwh_view”.“Dim_Date” “Dim_Date__via__BookedDateKey” ON “dwh_view”.“Fact_Supply_Chain_Shipments”.“BookedDateKey” = “Dim_Date__via__BookedDateKey”.“DateKey”
GROUP BY “Dim_Date__via__BookedDateKey”.“DateKey”

This is giving me “No results!” message.
thanks for the help!

2

Hi @Friz
What is a “datakey”? And which database are you querying?
You might get better help with straight SQL problems in a forum dedicated to your database.

3

Hi @flamber ,
datakey is the internal identifier of the date.

Unfortunately I could not find any forum dedicated to the database as it is a private one.

thanks for the input!

4

@Friz
I think you’re misunderstanding. We’re all querying private databases.
I’m asking which database type you’re querying - Postgres, SQL Server, MongoDB, SQLite, …?
If “datakey” is already a Date, then just convert it to DateTime, but you wouldn’t benefit from it, since you don’t have the time data.

5

Hi @flamber,

sorry my mistake! I am querying on MySQL. I must say I am quite inexperienced.

I try to be more specific with an example from the data as I am a bit vague: “Datakey” is in this format “20,190,725”. My thought was to convert it to a string “20190725” and then to a date time.
Does not know if it makes sense, thanks.

6

@Friz
Uhm, it sounds like the “datakey” column type is Integer instead of Date, but I still don’t understand why you’re trying to convert it to DateTime, when Date would be sufficient.

Not sure why your data structure doesn’t follow regular conventions, but if you cannot modify your structure, then create a View, where you do the conversion.

You can find a lot of excellent help on https://stackoverflow.com/questions/tagged/mysql about MySQL (and many other subjects).

7

@flamber Thanks for the suggestions, I will try them and see if I manage to run this query.

cheers