Convert Datetime - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Convert Datetime

Author

Topic

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-10-06 : 19:14:07

Hi all I am trying to give users the ability to see the year rather then the month the day and the year. I want to convert Finasuitdone DATETIME so that the users will see the year rather then the entire date. trying to incorporate that into this stored procedure

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Renewals]
(@StartFINALSUITDONE datetime,
@EndFINALSUITDONE datetime,
@FINALBYYR varchar(4))

AS
SELECT     TM#, LASTNAME, FIRSTNAME, CONDITIONAL, DATEOFCONDITIONAL, FINALSUITDONE, JOBTITLE, HIREDATE, FINALBYYR
FROM         dbo.EmployeeGamingLicense
WHERE     (FINALSUITDONE BETWEEN @StartFINALSUITDONE
 AND @EndFINALSUITDONE
 OR FINALBYYR = @FINALBYYR)


alter table dbo.EmployeeGamingLicense add FINALBYYR as reverse(cast(reverse([FINALSUITDONE]) as NVARCHAR(10)))

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-10-06 : 19:27:27

You should really do this in the front end, but you can do his using substring and convert functions.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 01:05:08

if you want to show year value alone use YEAR(FINALSUITDONE)

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-10-07 : 12:33:46

Showing the year is what I want, I just want to incorporate that into my Stored procedure. So that when they can enter in the year and see all the final suits for that year? Does that make sense??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 12:59:28

do u mean this?

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Renewals]
(@StartFINALSUITDONE datetime,
@EndFINALSUITDONE datetime,
@FINALBYYR varchar(4))

AS
SELECT     TM#, LASTNAME, FIRSTNAME, CONDITIONAL, DATEOFCONDITIONAL, YEAR(FINALSUITDONE), JOBTITLE, HIREDATE, FINALBYYR
FROM         dbo.EmployeeGamingLicense
WHERE     (FINALSUITDONE BETWEEN @StartFINALSUITDONE
 AND @EndFINALSUITDONE
 OR FINALBYYR = @FINALBYYR)

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-07 : 13:38:31

alter table dbo.EmployeeGamingLicense add FINALBYYR as YEAR(FINALSUITDONE)

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-10-07 : 13:43:58

YES THATS IT THANK YOU SO MUCH GUYS. I created that stored procedure already, but the thing was that I was not altering the table correctly so that FINALBYYR Was showing (YEAR) That was my big problem. Now I see hwo to do it. Thank you again, now i understand. I made this allot harder then what it really was

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 13:52:49

i didnt understand why you need a seperate column in table for storing year? you could still use datecolumn and apply YEAR() over it to get year of date and compare it with passed on year value @FINALBYYR. no need of seperately storing it in table at all.

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-10-07 : 14:59:57

ok thank you visakh16

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources