Displaying Date from month and year

Question

Post reply

Displaying Date from month and year

anitha.cherukuri

SSCommitted

Points: 1514
More actions
December 13, 2009 at 7:47 pm

#217573

Hi friends
I have year and period values in my table like this
2005 5
2001 12
2006 11
I want to show 20050501,20011201,20061101
any ideas please?

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

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

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 1

Are these stored in one field or multiple fields?

separate fields, use the + to contatinate values

select year + month + '01'

anitha.cherukuri SSCommitted Points: 1514 More actions · Answer 2

multiple fields,

i tried your solution but its giving inaccurate results like this

@year=2004

@period=9

select year+period+'01'

result: 2014

i wnat like this 20040900

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 3

If you are storing these as numerical values you will need to CAST them as strings.

select cast( year as char(4)) + cast( period as char(2)) + '01'

In the future, please include the DDL for the table.

anitha.cherukuri SSCommitted Points: 1514 More actions · Answer 4

Thanks Steve..

It works perfectly..awesome

this is final code..

cast( trn.TGJTYR as char(4)) + RTRIM(cast( trn.TGJTPD as char(2)))+ '01'

Paul White SSC Guru Points: 150467 More actions · Answer 5

I wouldn't expect that to work...? You need to prefix with zeroes in some circumstances. One solution is:

declare @year int,

@month int;

set @year = 2009;

set @month = 1

print right(10000 + @year, 4) + right(100 + @month, 2) + '01';

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

kevin_nikolai SSCarpal Tunnel Points: 4343 More actions · Answer 6

DECLARE @StartDate datetime, @EndDate datetime, @StartHour datetime, @EndHour datetime,

@liTimeOffset INT

SET @liTimeOffset = 120

SELECT DateAdd(n, @liTimeOffset, LocationDateTime)

FROM mytable

WHERE

LocationDateTime >= (CONVERT(VARCHAR(10), @StartDate, 120) + RIGHT(CONVERT(VARCHAR, @StartHour, 100),7))

AND

LocationDateTime < (CONVERT(VARCHAR(10), @EndDate, 120) + RIGHT(CONVERT(VARCHAR, @EndHour, 100),7))

LocationDateTime = '2009-11-01 08:13:02' (this is an example).

@StartHour and @EndHour is input parameters for stored procedure. When entering

2009-11-29 (@StartDate), 2009-11-30 (@EndDate), 22:00 (@StartHour), 03:00 (@EndHour)

the above works perfectly but I have one problem, if I want to use it as input parameters for SQL reporting services report it gives no result, if I specify @StartHour and @EndHour as datetime or Integer. If I specify as string it does return results, but more rows are returned that it should.

How do I resolve issue ?

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 7

clive-421796 (12/14/2009)
DECLARE @StartDate datetime, @EndDate datetime, @StartHour datetime, @EndHour datetime,
@liTimeOffset INT
SET @liTimeOffset = 120
SELECT DateAdd(n, @liTimeOffset, LocationDateTime)
FROM mytable
WHERE
LocationDateTime >= (CONVERT(VARCHAR(10), @StartDate, 120) + RIGHT(CONVERT(VARCHAR, @StartHour, 100),7))
AND
LocationDateTime < (CONVERT(VARCHAR(10), @EndDate, 120) + RIGHT(CONVERT(VARCHAR, @EndHour, 100),7))
LocationDateTime = '2009-11-01 08:13:02' (this is an example).
@StartHour and @EndHour is input parameters for stored procedure. When entering
2009-11-29 (@StartDate), 2009-11-30 (@EndDate), 22:00 (@StartHour), 03:00 (@EndHour)
the above works perfectly but I have one problem, if I want to use it as input parameters for SQL reporting services report it gives no result, if I specify @StartHour and @EndHour as datetime or Integer. If I specify as string it does return results, but more rows are returned that it should.
How do I resolve issue ?

DECLARE @StartDate datetime, @EndDate datetime, @StartHour datetime, @EndHour datetime,

@liTimeOffset INT

SET @liTimeOffset = 120

Since all the parameters are datetime...

SELECT DateAdd(n, @liTimeOffset, LocationDateTime)

FROM mytable

WHERE

LocationDateTime >= @StartDate+@StartHour

AND

LocationDateTime < @EndDate+@EndHour

--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)

kevin_nikolai SSCarpal Tunnel Points: 4343 More actions · Answer 8

kevin_nikolai

SSCarpal Tunnel

Points: 4343

December 16, 2009 at 1:16 pm

#1093277

Thanks Jeff, your solution worked.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 9

You're welcome. Thanks for the feedback, Clive.

--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)

kevin_nikolai SSCarpal Tunnel Points: 4343 More actions · Answer 10

Hi Jeff, the query which I included in my post also worked.

WHERE LocationDateTime >= (CONVERT(VARCHAR(10), @StartDate, 120) + RIGHT(CONVERT(VARCHAR, @StartHour, 100),7))

AND LocationDateTime < (CONVERT(VARCHAR(10), @EndDate, 120) + RIGHT(CONVERT(VARCHAR, @EndHour, 100),7))

In SQL Reports, you need to specify the @StartHour (19:00) and @EndHour (07:00) values as string (not datetime). This sorted the issue. I anyways used your method since its less code.

Thanks again.

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

clive-421796 (12/17/2009)
Hi Jeff, the query which I included in my post also worked.
WHERE LocationDateTime >= (CONVERT(VARCHAR(10), @StartDate, 120) + RIGHT(CONVERT(VARCHAR, @StartHour, 100),7))
AND LocationDateTime < (CONVERT(VARCHAR(10), @EndDate, 120) + RIGHT(CONVERT(VARCHAR, @EndHour, 100),7))
In SQL Reports, you need to specify the @StartHour (19:00) and @EndHour (07:00) values as string (not datetime). This sorted the issue. I anyways used your method since its less code.
Thanks again.

Exactly... less code. Also, fewer conversions.

--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)

Paul White SSC Guru Points: 150467 More actions · Answer 12

Jeff Moden (12/17/2009)
Exactly... less code. Also, fewer conversions.

And more index-friendly 😉

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi