How to separate date/time in SQL query

Question

Post reply

How to separate date/time in SQL query

velonia

SSC Enthusiast

Points: 172
More actions
June 26, 2018 at 12:55 pm

#104472

Hello,
I am fairly new to SQL and trying to write a simple query. The problem is the date/time field that I need to figure out how to separate:
So I have:
Select distinct player_id
from ticket
where create_date is between '2018-06-01' and '2018-06-26';
The problem is that create_date is configured as yyyy-mm-dd hh:m:ss format.
How do i separate the date?
Thank you

Viewing 8 posts - 1 through 7 (of 7 total)

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

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 1

Do you mean it's stored as plain text in that format and not a datetime?

velonia SSC Enthusiast Points: 172 More actions · Answer 2

it is stored as a datetime. I just need a date for that I could limit my queries to specific months. (I have attached a screenshot)

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 3

Then what's wrong with the original format you used for the query?

DesNorton SSC-Insane Points: 24276 More actions · Answer 4

velonia - Tuesday, June 26, 2018 12:55 PM
Hello,
I am fairly new to SQL and trying to write a simple query. The problem is the date/time field that I need to figure out how to separate:
So I have:
Select distinct player_id
from ticket
where create_date is between '2018-06-01' and '2018-06-26';
The problem is that create_date is configured as yyyy-mm-dd hh:m:ss format.
How do i separate the date?
Thank you

Your query will exclude all entries on the day of 2018-06-26 as it automatically uses a time of 00:00:00.000.
This query will include the last day.
SELECT DISTINCT player_id FROM ticket WHERE create_date >= '2018-06-01' AND create_date < '2018-06-27';

How to post data/code on a forum to get the best help.

velonia SSC Enthusiast Points: 172 More actions · Answer 5

The last post addressed my issue. Thank you for your help!

jasona.work SSC Guru Points: 50082 More actions · Answer 6

velonia - Tuesday, June 26, 2018 1:33 PM
It is giving me an error message. #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'between '2018-06-01' and '2018-06-26' LIMIT 0, 30' at line 3.
I need to separate date from time I guess. Thank you for your responses.

OK, part of the problem with any answers you get here is, this is a site for Microsoft SQL Server, not MYSQL. Someone *might* be able to answer your question here, it depends on if anyone has sufficient knowledge of MySQL. (For the record, I can't answer your question, I don't know enough about MySQL query syntax.)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

velonia - Tuesday, June 26, 2018 1:33 PM
It is giving me an error message. #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'between '2018-06-01' and '2018-06-26' LIMIT 0, 30' at line 3.
I need to separate date from time I guess. Thank you for your responses.

If using BETWEEN take out the IS.