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
 SQL Server Administration (2008)
 Convert Datetime field in my SQL Statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Liro0917
Starting Member

USA
7 Posts

Posted - 07/30/2013 :  11:14:33  Show Profile  Reply with Quote
In the Select statement below I need to convert datetime fields to show only the date in this format 'MMDDYYYY'.

I tried to use this:
cast(left(convert(char(8),getdate(),112),6) as int)

But where do I tell the statement to convert the specific field?
These are the two fields I need to change.

RR.eff_dt AS EffectiveDateKey,
RR.EXP_dt AS EndDateKey

Thanks!

Select
RR.Region as Region_Code,
RR.Region_Desc,
RR.IPOD_IPA as Region_Type,
RR.Risk,
RR.Market As Operational_Market,

Case

When RR.MKTDESC = 'NASHVILLE' then 'MIDDLE TENNESSEE'
When RR.MKTDESC = 'MEMPHIS' then 'WEST TENNESSEE'
When RR.MKTDESC = 'CHATTANOOGA'then 'EAST TENNESSEE'

ELSE 'UNKNOWN' END AS OperationalSubMarket,

RR.eff_dt AS EffectiveDateKey,
RR.EXP_dt AS EndDateKey

from dbo.R_CUST_REGIONS_RISK_SPANNED As RR

Thanks!
Liro

James K
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 07/30/2013 :  12:38:30  Show Profile  Reply with Quote
Replace that GETDATE() with the column names. So for example:
cast(left(convert(char(8),RR.eff_dt ,112),6) as int)
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30241 Posts

Posted - 07/30/2013 :  12:38:41  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SELECT	REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '')



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Liro0917
Starting Member

USA
7 Posts

Posted - 07/30/2013 :  13:14:18  Show Profile  Reply with Quote
quote:
Originally posted by James K

Replace that GETDATE() with the column names. So for example:
cast(left(convert(char(8),RR.eff_dt ,112),6) as int)





This worked Perfectly! Thank you! Thank you!

Thanks!
Liro
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30241 Posts

Posted - 07/30/2013 :  15:57:50  Show Profile  Visit SwePeso's Homepage  Reply with Quote
100 * YEAR(rr.eff_dt) + MONTH(rr.eff_dt)



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
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.08 seconds. Powered By: Snitz Forums 2000