Help with date conversion - t-sql

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 2005 Forums

Transact-SQL (2005)

Help with date conversion - t-sql

Author

Topic

mob_obrienm
Starting Member

6 Posts

Posted - 2008-02-25 : 12:17:31

Hello,
I'm working on some date conversions and having a few issues. In Oracle, I would have wrote something like this to insert/update the date value in this column. You'll see, the column month is a varchar type:
****TABLE STRUCTURE***
create table mikenptest (
month varchar(5) NOT NULL,
status varchar(10) NOT NULL,
date_upd date NOT NULL
);
****INSERT(Oracle)*******
INSERT INTO mikenptest (month, status, date_upd) VALUES (to_char(add_months(sysdate,-1), 'MON'), 'BAD', sysdate);
****UPDATE(Oracle)*******
UPDATE mikenptest
SET month = to_char(add_months(sysdate,-2), 'MON')
WHERE status = 'BAD'
**************************
Basically, all it does is changes the date format to a VARCHAR entry for months using (DEC, JAN, FEB, etc).
I'm trying to do the same for sql server, but am not sure how to manipulate the date to become a varchar. So, i would need something using the datadd function maybe, and then convert to a string? Any feedback/help would be greatly appreciated. Thanks in advance for any help you can provide. Let me know if you need any more information.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-25 : 12:24:10

[code]INSERT INTO mikenptest (month, status, date_upd) VALUES
(DATENAME(mm,DATEADD(mm,-1,GETDATE())),'BAD',GETDATE())

UPDATE mikenptest
SET month = DATENAME(mm,DATEADD(mm,-2,GETDATE()))
WHERE status = 'BAD'[/code]

mob_obrienm
Starting Member

6 Posts

Posted - 2008-02-25 : 12:51:55

Thanks so much for the quick response! This looks extremely close to what I'm looking for. I may need to force upper case on the month and trim to include the just the first 3 characters, but I will start looking at that next. Thanks again, that definitely helped me pull in the previous months value and pointed me in the right direction.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-25 : 12:56:43

Use UPPER() to convert to upper case & LEFT() to get substring from left.

mob_obrienm
Starting Member

6 Posts

Posted - 2008-02-25 : 13:04:54

Perfect, thanks again!

INSERT INTO mikenptest (month, status, date_upd) VALUES
(UPPER(LEFT(DATENAME(mm,DATEADD(mm,-2,GETDATE())),3)),'BAD',GETDATE())

UPDATE mikenptest
SET month = UPPER(LEFT(DATENAME(mm,DATEADD(mm,-1,GETDATE())),3))
WHERE status = 'BAD'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources