conver datetime to varchar give only "1"

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 2008 Forums

Transact-SQL (2008)

conver datetime to varchar give only "1"

Author

Topic

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-10-27 : 07:28:38

[code]
DECLARE @DateFrom as DateTime,
@DateTo as DateTime
declare @datetimefromnv as varchar
declare @datetimetonv as varchar

SET @DateFrom = CONVERT(DATETIME,CONVERT(VARCHAR,DATEADD(hh,-6,getdate()),101))--CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE()-1,101))
SET @DateTo = CONVERT(DATETIME,CONVERT(VARCHAR,DATEADD(hh,-6,getdate()+1),101))--CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),101))

set @datetimefromnv = LEFT(CONVERT(VARCHAR, @DateFrom, 101), 10)
select @datetimefromnv

[/code]
@datetimefromnv gives out 1.
I would have expected '20141027 00:00:00.000'
what am i doing wrong?
thanks.

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-10-27 : 08:13:39

try this
DECLARE @DateFrom as DateTime,
@DateTo as DateTime
declare @datetimefromnv as DateTime
declare @datetimetonv as DateTime

SET @DateFrom = CONVERT(DATETIME,CONVERT(VARCHAR,DATEADD(hh,-6,getdate()),101))--CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE()-1,101))
SET @DateTo = CONVERT(DATETIME,CONVERT(VARCHAR,DATEADD(hh,-6,getdate()+1),101))--CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),101))

set @datetimefromnv = LEFT(CONVERT(VARCHAR, @DateFrom, 101), 10)
select @datetimefromnv

Javeed Ahmed

mhorseman
Starting Member

44 Posts

Posted - 2014-10-27 : 08:16:34

You've not specified lengths on your varchars, so their default length is 1.

If you're trying to get a 10 character string with the date in YYYY-MM-DD format, try:

declare @datetimefromnv as varchar(30)

select @datetimefromnv = convert(varchar(10),getdate(),120)

select @datetimefromnv

Mark

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-10-27 : 09:12:56

Hi.Thanks, another issues here.
If i do this:

 	DECLARE @DateFrom as DateTime,
@DateTo as DateTime
declare @datetimefromnv as DateTime
declare @datetimetonv as DateTime


SET @DateFrom = CONVERT(DATETIME,CONVERT(VARCHAR,DATEADD(hh,-6,getdate()),101))--CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE()-1,101))
SET @DateTo = CONVERT(DATETIME,CONVERT(VARCHAR,DATEADD(hh,-6,getdate()+1),101))--CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),101))

select @datefrom

i get:
2014-10-27 00:00:00.000

however if i do this:


declare @datetimefrommv as varchar(30)
 set @datetimefrommv = CONVERT(nvarchar(30), CONVERT(VARCHAR,DATEADD(hh,-6,getdate()),120), 101)

 select @datetimefrommv

i get:
2014-10-27 09:12:38
am i doing it wrong?

mhorseman
Starting Member

44 Posts

Posted - 2014-10-27 : 09:59:57

I'm presuming you now want to have @datetimefromv set to 6 hours before the current time, so 2014-10-27 09:12:38 would be correct. The first version loses the hours, minutes and seconds in one of the CONVERTs.

Perhaps you could explain what you actually want to see?

Mark

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-10-27 : 20:15:15

2014-10-27 00:00:00.000 . Wasn't my query so i really do not know why so much trouble for a wrong conversion.
Will have to ask that./

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources