HELP NEEDED WITH DATE FUNCTION

Question

Post reply

HELP NEEDED WITH DATE FUNCTION

Best-330874

SSCrazy

Points: 2350
More actions
November 10, 2006 at 1:46 pm

#92039

hi, I need help in my proc
i have a proc It works fine but the only problem i see in this
is that after i execute the proc and go in the table "test"
in one of the columns " TableValue"
i have a value like this Dec 1 2004 12:00AM
i want this value to be like in this format yyyy/mm/dd
Note :Column "Tablevalue datatype is varchar(100)
-------------------------------------------------------------
CREATE PROCEDURE GE_Test
       @Month_of_file_filter datetime
AS
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
CREATE TABLE [dbo].[test] (
       [Month_of_file] [datetime] NULL ,
       [CalcAction] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
       [TableValue] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
       [CalculatedValue] [int] NULL
) ON [PRIMARY]
end
INSERT INTO test (Month_of_file, CalcAction, TableValue, CalculatedValue)
       SELECT @Month_of_file_filter as Month_of_file, 'Record Count by Transaction Month' as CalcAction,
       MonthReported as TableValue, COUNT(*) as CalculatedValue
       FROM GE_Transaction
       WHERE Month_of_file = @Month_of_file_filter
       GROUP BY MonthReported
GO

Viewing 4 posts - 1 through 3 (of 3 total)

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

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 1

Try this:

INSERT INTO test (Month_of_file, CalcAction, TableValue, CalculatedValue)

SELECT @Month_of_file_filter as Month_of_file, 'Record Count by Transaction Month' as CalcAction,

Convert(Char(10),MonthReported,111) as TableValue, COUNT(*) as CalculatedValue

FROM GE_Transaction

WHERE Month_of_file = @Month_of_file_filter

GROUP BY MonthReported

SQLBill SSC Guru Points: 51440 More actions · Answer 2

SQL Server recognizes that as a valid date (Dec 1 2004 12:00AM). So, from here it depends on what you want to do. Do you just want to display the date as yyyy/mm/dd? Or do you want to store it that way?

Changing it to DATETIME does not store it in any specific format. It is stored as an eightbyte value.

If you want it stored as a string or displayed that way use CONVERT.

SELECT CONVERT(VARCHAR(10), 'Dec 1 2004 12:00AM', 111)

-SQLBill

Best-330874 SSCrazy Points: 2350 More actions · Answer 3

Thanks a lot

Take care

Any way my name is asim siddiqui and my e-mail address

is abold123@gmail.com .i was wondering if i can get your email for any future reference or question if it is ok with you.