weird date format problem - 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)

weird date format problem

Author

Topic

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-09-13 : 17:51:32

hi friends
am running following script in query analyzer

DECLARE @pEnddate datetime, @pTransactdate varchar(max)
SET @pTransactdate = '2005-03-02 11:28:36.000'
SELECT @pEnddate = CONVERT(smalldatetime,CONVERT(varchar(8),CONVERT(datetime,@pTransactdate),112),120)
SELECT @pEnddate

and above returns date in following format which is what i want
2005-03-02 00:00:00.000

interestingly am using same code in a SP but getting date in following format
"Mar 2 2005 12:00AM" !!

why same code produces different output ?
am using sql2005 standard edition.

Cheers

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-13 : 18:13:34

You're doing an awfull lot of CONVERTing there which is unnecessary. Since @pEnddate is defined as datetime that is how the value is stored. As all datetime values are stored which is a pair of ints. The presentation of the value is determined by what you are using to SELECT it.

If you want to simply strip the time portion of the datetime down to 0, DATEADD will be more efficient because you reduce the number of converts.


DECLARE	@pEnddate datetime
	,@pTransactdate varchar(50)

SET @pTransactdate = '2005-03-02 11:28:36.000'

SELECT @pEnddate = dateadd(day, datediff(day, 0, @pTransactdate), 0)


SELECT	@pTransactdate [@pTransactdate]
	,@pEnddate as [@pEnddate]
	,convert(varchar, @pEnddate, 100) as [formattedVarcharStyle100]

output:
@pTransactdate                    @pEnddate                  formattedVarcharStyle100       
------------------------------------------------------------------------------------------- 
2005-03-02 11:28:36.000           2005-03-02 00:00:00.000    Mar  2 2005 12:00AM

Be One with the Optimizer
TG

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-09-13 : 18:40:17

Thanks TG, ur code worked nicely.
but still my question remains why same code displays 2 different results

Cheers

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-13 : 18:42:42

rajani,

Where are you seeing the stored procedure results? Via your application or Query Analyzer/Management Studio?

Tara Kizer

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-09-13 : 18:56:17

i tried in sql Management Studio

Cheers

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-13 : 19:33:30

quote:
interestingly am using same code in a SP but getting date in following format
"Mar 2 2005 12:00AM" !!

How do you show te date in your SP ? are you using a PRINT ?

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-09-13 : 19:50:19

exactly same as my first post.
i.e. am using "SELECT"

Cheers

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-09-13 : 19:50:43

i even tried sp_recompile but to no avail.

Cheers

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-13 : 22:11:10

i create the following on SQL Server 2005 using Query Analyzer (from 2000) to connect to the server.


create procedure ssp_test
as
begin
	DECLARE @pEnddate datetime, @pTransactdate varchar(max)
	SET 	@pTransactdate = '2005-03-02 11:28:36.000'
	SELECT 	@pEnddate = CONVERT(smalldatetime,CONVERT(varchar(8),CONVERT(datetime,@pTransactdate),112),120)
	SELECT 	@pEnddate
end

select @@VERSION
-- Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)   Oct 14 2005 00:33:37   Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1) 

exec ssp_test
-- 2005-03-02 00:00:00.000

DECLARE @pEnddate datetime, @pTransactdate varchar(max)
SET 	@pTransactdate = '2005-03-02 11:28:36.000'
SELECT 	@pEnddate = CONVERT(smalldatetime,CONVERT(varchar(8),CONVERT(datetime,@pTransactdate),112),120)
SELECT 	@pEnddate
-- 2005-03-02 00:00:00.000

How is your testing query like ?

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-09-13 : 22:39:35

i tried ur code and it works as expected.
but some reason my stored proc does not!! anyway it does not worth the time investigating any further.
thanks.

Cheers

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-13 : 22:45:15

quote:
Originally posted by rajani

i tried ur code and it works as expected.
but some reason my stored proc does not!! anyway it does not worth the time investigating any further.
thanks.

Cheers

How is your stored procedure looks like ? Mind to post it here ?

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-09-13 : 22:55:53

something like

CREATE PROCEDURE dbo.del
@pTransactdate varchar(50) =NULL,
@pCreationDate varchar(50) = NULL,
@pProviderIds varchar(max),
@pRevenueIds varchar(max),
@pCountryFlavour varchar(max)='NZ'
AS
SET NOCOUNT ON
DECLARE @pEnddate datetime
SET @pTransactdate = '2005-03-02 11:28:36.000'
SELECT @pEnddate = CONVERT(smalldatetime,CONVERT(varchar(8),CONVERT(datetime,@pTransactdate),112),120)
SELECT @pEnddate
--some sql statement
SELECT * FROM dbo.u_master
Go

interestingly i created another procedure (with different name) with same code and it works!!
may be some bug in sql server

Cheers

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-13 : 23:01:02

Indeed strange !

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-14 : 00:50:24

No, it is not. The last conversion is to smalldatetime, and then a select.
Selecting a variable of type smalldatetime always returns in locale format, because the date is stored natively.

Peter Larsson
Helsingborg, Sweden

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-09-14 : 00:59:00

quote:
Originally posted by Peso

Selecting a variable of type smalldatetime always returns in locale format, because the date is stored natively.

Peter Larsson
Helsingborg, Sweden

even if we use CONVERT function to change format ?

Cheers

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-14 : 01:08:12

Look at this difference in output

set nocount on

print  CONVERT(smalldatetime, CONVERT(varchar(8), CONVERT(datetime, getdate()), 112), 120)
select CONVERT(smalldatetime, CONVERT(varchar(8), CONVERT(datetime, getdate()), 112), 120)

Peter Larsson
Helsingborg, Sweden

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-09-14 : 01:13:55

wow! i did not know tht before.
is there any good reason why they do like that ?

Cheers

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-09-14 : 01:23:51

btw in my example i never used "print" command so it should have displayed date in the format i wanted though.

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources