Convert Datetime field in my SQL Statement

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)

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

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources