x

Date Format

Anyone know why when i run this query the Yr Column in coming back 203111 instead of 2011

 SELECT  name, 
 count (DISTINCT Convert(varchar(10), finishdate, 120)) as fdate ,
 year(finshdate) * 100 + datepart(YY, finshdate) as YR    
 FROM t1  
 Group BY name, year(finishdate) * 100 + datepart(YY,finishdate)
more ▼

asked Aug 10, 2011 at 11:03 AM in Default

sqlLearner 1 gravatar image

sqlLearner 1
792 36 39 46

(comments are locked)
10|1200 characters needed characters left

3 answers: sort newest

Multiplying a year by 100 to then add the month number on it is not the way to work with date. SQL Server can be a bit awkward when things get complex but in general the date functions let you achieve what you want with pretty simple code. @ThomasRushton has given you some good examples of getting the value you want.

For me, if you want a year and month combination that is YYYYMM then you wont be doing anything with it that involves calculations (If you were - such as adding some months onto a YYYYMM combination then let us know as this is not the best way to go.) then I would change the Year and Month value datatypes to CHAR and then the + becomes a concatenating function rather than a summing function. So...

DECLARE @tbl TABLE ( MyDate DATE )

INSERT  INTO @tbl
    ( [MyDate] )
VALUES  ( '2011-08-10'  -- MyDate - date
      ),
    ( '2010-03-11'  -- MyDate - date
      ),
    ( '2001-08-21'  -- MyDate - date
      )

SELECT  CONVERT(CHAR(4), YEAR([t].[MyDate])) + CONVERT(CHAR(2), REPLICATE('0',
                                                          2
                                                          - LEN(MONTH([t].[MyDate])))
    + CONVERT(CHAR(2), MONTH([t].[MyDate]))) AS [Date_as_YYYYMM]
FROM    @tbl AS t
more ▼

answered Aug 10, 2011 at 02:00 PM

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

@Fatherjack - couldn't it be better expressed just using the CONVERT command and the date style option?

SELECT CONVERT(varchar(6),MyDate,112) FROM @tbl
The date style 112 is the ISO standard YYYYMMDD, converting the date to a varchar(6) will return the first 6 characters of that date style, job done.
Aug 10, 2011 at 11:09 PM WilliamD
@WilliamD - yeah probably. It was late!
Aug 11, 2011 at 12:10 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

If we rewrite the query:

declare @finishdate datetime = getdate()
SELECT  
    YEAR(@finishdate),   
    year(@finishdate) * 100,   
    DATEPART(YY,@finishdate),
    year(@finishdate) * 100 + datepart(YY, @finishdate)

This returns:

2011          201100       2011       203111
What you're doing is multiplying the year by 100 and then adding it to itself. Or, indeed, multiplying it by 101. Whichever. This is almost certainly not what you were aiming to achieve. If you just want the year, then use either YEAR(finishdate) or Datepart(YY,finishdate) - there's no need to use both.
more ▼

answered Aug 10, 2011 at 11:42 AM

ThomasRushton gravatar image

ThomasRushton ♦
33.9k 18 20 44

(comments are locked)
10|1200 characters needed characters left
When I run your query and substitute (finishdate) with getdate() I get correct results. Since you are pulling from a table T1 for a distinct value from column finishdate, have you validated that you do not in fact have an erroneous year value of 2031 in your data set?
more ▼

answered Aug 10, 2011 at 11:06 AM

Tim gravatar image

Tim
36.4k 37 41 139

No, I don;t have erroneous date. It works when I change the YY to M I would get a result 201101 for Jan 2011. But when I change it to YY it returns 203111
Aug 10, 2011 at 11:14 AM sqlLearner 1

Ah, I spaced on that. My apologies. I was looking at the fdate portion of your query. So your year you are taking the year of finishdate multiplying by 100 and adding it to the year of finishdate. So 201100 plus 2011 = 203111.

What exactly is the outcome you are wanting? A value of 2011, 201100, 201111, or 2011002011?
Aug 10, 2011 at 11:22 AM Tim
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x986
x28

asked: Aug 10, 2011 at 11:03 AM

Seen: 702 times

Last Updated: Aug 10, 2011 at 01:47 PM