Can we access the special point of something? I have a database that stores time in this format: year/month/day/hour/minute

Question

If I wanna access just to year, should I store it in a seperate column or I can have access to the year part seperately in the format above?

Answer 1

Wow! Such powerful information Kail Galestorm
Thanks a lot
Could you please explain the last part you've mentioned? How does it work?

Answer 2

Kail Galestorm thanks a lot🙏😊
I got the whole point

Answer 3

This format is fine and there is no need for an extra "Year" column. If you are asking how to filter based only on one part of the date time column. This would be done in what ever is accessing the data. For instance, if you are using an SQL query, it would be something like
Select *
From <table>
Where <DateTimeColumn> like '2019/%'
So whatever is accessing the data will sort based on what you need.
If you are asking how to only show the Year part of the column it would be something like
Select substring(<DateTimeColumn>, charindex('/', <DateTimeColumn>)+1,len(<DateTimeColumn>)- harindex('/', <DateTimeColumn>)) as [Year]

Answer 4

Well... After sitting down at my computer and looking at it, I gave a bad example that wont work.
In my second example I wanted to return just the year part of the column which is everything up to the first /, or everything left of the first /. In my example I used substring, which takes in:
SUBSTRING(input_string, starting point, number of characters to return)

So the input string would be your column,
then I used CHARINDEX to find the starting point of the first / it could find.
I then use LEN, to get the length of the value in the column, and subtract CHARINDEX of the first /.
Unfortunately this would result in giving you all characters to the right of your first /. Because, the first / is the 5th character and in your example format "year/month/day/hour/minute" there are 26 characters so, I told substring to start at the 5th character and return 26-5 characters. It would have returned "month/day/hour/minute"...
There is a way that can work but there is a much easier way to go about things.

LEFT(input_string, number of characters to return to the left)

We still should use CHARINDEX to find out how many characters are between the beginning of the string and the first /. So your statement would look like this:

SELECT
LEFT(DateTimeColumn, CHARINDEX('/',DateTimeColumn)-1) AS [Year]
FROM YourData

So The first argument in LEFT is your column, the second is returning the position of the first / as a number. in your example format "year/month/day/hour/minute" the position of the first / is 5. We do not want to return "year/" so we subtract 1 from CHARINDEX. LEFT now looks like this after executing CHARINDEX:
SELECT
LEFT(DateTimeColumn, 5 - 1) AS [Year]
FROM YourData

Last we have the "AS [Year]". Because we are using LEFT on the column we are selecting, the original name of that column is obfascated so we are just renaming it to "Year". Otherwise the column will have a blank header in the results.