How to trim sql query

Question

How to trim sql query

elmnas

SSC-Addicted

Points: 412
More actions
September 30, 2015 at 6:22 am

#311930

Hello people I have made following query,
How do I trim my result without replace anything in the current DB?
here is my query
SELECT
tt.bestdatum as 'Year',
FROM
[myDB].dbo.myTable as tt INNER JOIN
WHERE (DATEPART(year, tt.bestdatum) = '2015')
I get this result
2015-09-21 15:49:28
I need one column that just says:
2015
and one column that says:
09
Could someone help me?
Thank you in advance

Viewing 12 posts - 1 through 11 (of 11 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

elmnas (9/30/2015)
Hello people I have made following query,
How do I trim my result without replace anything in the current DB?
here is my query
SELECT
tt.bestdatum as 'Year',
FROM
[myDB].dbo.myTable as tt INNER JOIN
WHERE (DATEPART(year, tt.bestdatum) = '2015')
I get this result
2015-09-21 15:49:28
I need one column that just says:
2015
and one column that says:
09
Could someone help me?
Thank you in advance

SELECT YEAR(GETDATE()), MONTH(GETDATE())

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ant-Green SSC Guru Points: 113507 More actions · Answer 2

Ant-Green

SSC Guru

Points: 113507

September 30, 2015 at 6:49 am

#1830390

Take a look at DATEPART

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

elmnas (9/30/2015)
Hello people I have made following query,
How do I trim my result without replace anything in the current DB?
here is my query
SELECT
tt.bestdatum as 'Year',
FROM
[myDB].dbo.myTable as tt INNER JOIN
WHERE (DATEPART(year, tt.bestdatum) = '2015')
I get this result
2015-09-21 15:49:28
I need one column that just says:
2015
and one column that says:
09
Could someone help me?
Thank you in advance

The answer is the same in the SELECT list as what you have in the WHERE clause. Of course, that's assuming that your query isn't in the category of "without replace anything in the current DB".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101103 More actions · Answer 4

From a performance standpoint, do this, get rid of the functions against the column in the WHERE clause to allow max possible use of all indexes. Always avoid functions against table columns unless they are absolutely necessary.

Btw, add DISTINCT if you want to make sure you never get duplicate rows for the same month:

SELECT --DISTINCT

YEAR(tt.bestdatum) as 'Year',

MONTH(tt.bestdatum) AS 'Month'

FROM

[myDB].dbo.myTable as tt INNER JOIN ...

WHERE tt.bestdatum >= '20150101' AND tt.bestdatum < '20160101'

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

elmnas SSC-Addicted Points: 412 More actions · Answer 5

ScottPletcher (9/30/2015)
From a performance standpoint, do this, get rid of the functions against the column in the WHERE clause to allow max possible use of all indexes. Always avoid functions against table columns unless they are absolutely necessary.
Btw, add DISTINCT if you want to make sure you never get duplicate rows for the same month:
SELECT --DISTINCT
YEAR(tt.bestdatum) as 'Year',
MONTH(tt.bestdatum) AS 'Month'
FROM
[myDB].dbo.myTable as tt INNER JOIN ...
WHERE tt.bestdatum >= '20150101' AND tt.bestdatum < '20160101'

Is this command that change the database or is this a safe method?

Ant-Green SSC Guru Points: 113507 More actions · Answer 6

What do you mean by change the database?

Is this going to go into a stored proc?

Is this a one off ad-hoc query?

Dee-ZA Old Hand Points: 347 More actions · Answer 7

DECLARE @bestdatum DATETIME

SET @bestdatum = '2015-09-21 15:49:28'

SELECT YEAR(@bestdatum) Year

,MONTH(@bestdatum) Month

,DAY(@bestdatum) Day

-------------------------------------------------------------------------------

--Just put your date in the YEAR() function and compare it to your constant.

WHERE YEAR(@bestdatum) = '2015'

--------------------------------------------------------------------------------

Ant-Green SSC Guru Points: 113507 More actions · Answer 8

itumelengd (10/1/2015)
DECLARE @bestdatum DATETIME
SET @bestdatum = '2015-09-21 15:49:28'
SELECT YEAR(@bestdatum) Year
,MONTH(@bestdatum) Month
,DAY(@bestdatum) Day
-------------------------------------------------------------------------------
--Just put your date in the YEAR() function and compare it to your constant.
WHERE YEAR(@bestdatum) = '2015'
--------------------------------------------------------------------------------

As Scott mentioned the YEAR in the WHERE clause makes the query NONSARGABLE, meaning it will result in a full scan of the index/table

The better option is as Scott mentioned date >= '2015-01-01' and date < '2016-01-01' which is SARGABLE and will use the indexes as they are designed

elmnas SSC-Addicted Points: 412 More actions · Answer 9

I meant I don't want to change any table in the database I just want to collect data and I modify the data.

Ant-Green SSC Guru Points: 113507 More actions · Answer 10

You don't need to modify the table, you have a number of options to use when querying the table to get Month and Year from a date column

YEAR

MONTH

DATEPART

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 11

elmnas (10/1/2015)
I meant I don't want to change any table in the database I just want to collect data and I modify the data.

Then what I said in my original post about the answer being in you WHERE clause is true.

If we look at your original post

elmnas (9/30/2015)
Hello people I have made following query,
How do I trim my result without replace anything in the current DB?
here is my query
SELECT
tt.bestdatum as 'Year',
FROM
[myDB].dbo.myTable as tt INNER JOIN
WHERE (DATEPART(year, tt.bestdatum) = '2015')
I get this result
2015-09-21 15:49:28
I need one column that just says:
2015
and one column that says:
09
Could someone help me?
Thank you in advance

... you're looking to return just the year of the "bestdatum" column and the same formula that you have in your WHERE clause will do the trick if you use it in the SELECT clause.

As the others have stated, though, using such a formula around a column in the WHERE clause will make your query do only table or index scans as those are really slow and resource intensive compared to when an index seek and embedded range scan occurs. You've also hardcoded the year and that's a bad thing, as well, because you have to remember to change the code every time the year changes.

With that in mind, here are the "guts" of what stored procedure would look like to do what you need. I left it as a query instead of converting it to an SP because you haven't said how you will be calling the code or from where.

--===== These simulate the input parameter of a stored procedure or Inline Table Valued Function

DECLARE @pYear INT; --This would be the input parameter for a proc or iTVF

SELECT @pYear = 2015; --This simulates the input to the proc or iTVF

--===== This would be the body of the proc or the iTVF

SELECT [Year] = DATEPART(yy,tt.BestDatum)

,--other columns here

FROM MyDB.dbo.Mytable AS tt

--add joins to other tables here if needed

WHERE tt.BestDatum >= DATEADD(yy,@pYear-1900,0) --This makes it possible to use an index properly.

AND tt.BestDatum < DATEADD(yy,@pYear-1899,0) --This makes it possible to use an index properly.

;

The "0" in the DATEADDs is shorthand for '1900-01-01' as a date/time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)