CODEWITHSUNDEEP
sqlsql-serverdatedatetimestored-procedures
Mike
Mike

Reputation: 843

SQL Server: replace year of datetime with current year in Select

I am selecting data using the following stored procedure with dateDT being saved as datetime.

Can someone here tell me how I can replace the year in the below Convert results so that this is always the current year (independent of the year it is saved under in the table) ?

SELECT      dateID,
            CONVERT(VARCHAR(11), dateDT, 106) AS dateDT,
            CONVERT(VARCHAR(10), dateDT, 126) AS dateDTShort,
            countries,
            regions
FROM        DaysFixed
WHERE       countries LIKE '%'+@selCountry+'%'
OR          regions LIKE '%'+@selCountry+'%'
ORDER BY    dateID
FOR XML PATH('datesDT'), ELEMENTS, TYPE, ROOT('root')

Many thanks in advance, Mike.

Upvotes: 2

Views: 4442

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

You can change the year after the conversion. Here are examples for your date formats:

        CONVERT(VARCHAR(7), dateDT, 106) + CAST(year(getdate() as varchar(4)) AS dateDT,
        STUFF(CONVERT(VARCHAR(10), dateDT, 126), 1, 4, CAST(year(getdate() as varchar(4)))
             ) AS dateDTShort,

Upvotes: 2

M.Ali
M.Ali

Reputation: 69524

DECLARE @dt DATETIME = '2012-04-27 14:00:39.343'

SELECT CAST(
        STUFF(
              CONVERT(VARCHAR(23), @dt,121)
              ,1, 4, CAST(YEAR(GETDATE()) AS VARCHAR(4))
             )
           AS DATETIME)

 Result:  2014-04-27 14:00:39.343

Upvotes: 2

Related Questions