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)
 CONVERSION ERROR IN SQL SERVER 2008

Author  Topic 

kond.mohan
Posting Yak Master

213 Posts

Posted - 2013-02-16 : 06:24:23
HI ALL

I AM EXECUTING BELOW QUERY ON ssms i got the error on LOGINDATE COLUMN.I HAVE DONE EVENT_TIME COLUMN WITH ADDITION OF 5.5/24 HURS
(STANARD TIME). HOW CAN CONVERT WITH SUCCEFULLY OF THAT TYPE OF ISSUES.

ERROR :Explicit conversion from data type numeric to date is not allowed.
SELECT LOGGED_IN_USER_ID,
cast(MIN((EVENT_TIME)+5.5/24) as date)AS LOGINDATE,
SUBSTRING((Min(EVENT_TIME)),12,22) AS LOGINTIME
FROM dwh_staging..SSO_AUDIT_TBL
WHERE EVENT_ID = 'LOGIN'
AND EVENT_TIME BETWEEN '20130215' AND '20130216'
GROUP BY LOGGED_IN_USER_ID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-16 : 07:12:18
make it like


SELECT LOGGED_IN_USER_ID,
cast(DATEADD(dd,(5.5/24),MIN((EVENT_TIME))) as date)AS LOGINDATE,
SUBSTRING((Min(EVENT_TIME)),12,22) AS LOGINTIME
FROM dwh_staging..SSO_AUDIT_TBL
WHERE EVENT_ID = 'LOGIN'
AND EVENT_TIME BETWEEN '20130215' AND '20130216'
GROUP BY LOGGED_IN_USER_ID


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

kond.mohan
Posting Yak Master

213 Posts

Posted - 2013-02-18 : 00:12:49
THX VISHAK
and pls provide the sql server format for below code.
TO_CHAR(MIN(EVENT_TIME)+5.5/24,'HH24:MI:SS') AS LOGINTIME
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-18 : 00:18:58
CONVERT(varchar(8),DATEADD(dd,(5.5/24),MIN(EVENT_TIME)),108) AS LOGINDATE

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -