SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 CONVERSION ERROR IN SQL SERVER 2008		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kond.mohan
Posting Yak Master

India
138 Posts
Posted - 02/16/2013 :  06:24:23  Show Profile  Reply with Quote
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

India
47173 Posts
Posted - 02/16/2013 :  07:12:18  Show Profile  Reply with Quote
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

India
138 Posts
Posted - 02/18/2013 :  00:12:49  Show Profile  Reply with Quote
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

India
47173 Posts
Posted - 02/18/2013 :  00:18:58  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000