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
Go to Top of Page

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
Go to Top of Page

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?
Go to Top of Page

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
Go to Top of Page

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./
Go to Top of Page
   

- Advertisement -