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
 SQL Server Administration (2008)
 Convert Datetime field in my SQL Statement

Author  Topic 

Liro0917
Starting Member

7 Posts

Posted - 2013-07-30 : 11:14:33
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-30 : 12:38:30
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

30421 Posts

Posted - 2013-07-30 : 12:38:41
[code]SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '')[/code]


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

Liro0917
Starting Member

7 Posts

Posted - 2013-07-30 : 13:14:18
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

30421 Posts

Posted - 2013-07-30 : 15:57:50
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
   

- Advertisement -