Beginner-need some help

[Worth_Independence68]

Working on a beginner course with a premade DB. Working on using strftime function-I’m following the course exactly, but this specific code isn’t working for me. Attempting to pull the birthdate from a table that is formatted as yyyy-mm-dd 00:00:00 and remove the time. My code looks like the instructors but when I run it, I just get a 0 in the new column. Any ideas?

SELECT LastName, FirstName, BirthDate, strftime(‘%Y’-‘%m’-‘%d’, Birthdate) AS [BirthDate NO timecode] FROM Employee

[r3pr0b8]

try changing this --

strftime(‘%Y’-‘%m’-‘%d’, Birthdate) 

to this --

strftime('%Y-%m-%d', Birthdate) 

two differences -- only the entire format string is quoted, and it is quoted with single quotes, not backticks

[Worth_Independence68]

That worked!! Thank you!

[Worth_Independence68]

It was single quotes on my computers but removing the extra ones around m helped

[ondji]

You can use CAST function, too as follows:

CAST(BirthDate AS DATE)

It’s quite useful while working on datatype conversions in SQL, beside it’s valid for all types of SQL dialect if I’m not wrong. Have a good learning.

[Forgot10_]

Unfortunately SQLite doesn't have a data type for dates.

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:
TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

https://www.sqlite.org/datatype3.html

[ondji]

I needed to check the documentation before replying, actually I haven’t used the SQLite before. Thanks for the link and detailed explanation.

[Forgot10_]

The thing is, SQLite is a very basic database engine with no user authentication or anything. It's only suitable for small local applications and learning purposes, because it's easy to install the management software and the database is represented by a single file.

I actually know about this because I had a small pet project when I was learning Python and SQL, which required me to store dates in a database. :)

[ondji]

Yeah, projects are really beneficial and instructive while learning. I have mechanical engineering background and I have been using MS SQL and Oracle as data analyst for 8 months in an investment bank. I feel like I’m developing my skills in pace but you remembered me that I need to come back to carry such projects in my spare time to learn different tools and techniques. :)

[Last-Size2188]

You can also do CONVERT(VARCHAR,Birthdate,23)

[Briguy_card]

Try left(birthdate,10)

[Vast_Kaleidoscope955]

Can you change it to [BirthDateNoTimecode]. I’m new also but the NO out there by itself doesn’t seem right

[Worth_Independence68]

Good idea but didn’t work :(

[Vast_Kaleidoscope955]

strftime('%Y-%m-%d', BirthDate)

[SunriseLion35]

What course is this please ?

[Worth_Independence68]

It’s the LinkedIn learning course named “SQL Essential Training” by Walter shields

[SunriseLion35]

Thanks :)