Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

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

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help needed in Inserting/Updating specific dates
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mssrone
Starting Member

2 Posts

Posted - 05/06/2013 :  11:16:08  Show Profile  Reply with Quote
Hello,

I am trying to write some SQL queries to do following activity. Can you help me with you suggestions..

A). I need to INSERT a date in the format ‘YYYY-MM-01’ to the date field where YYYY is current year and MM is current month + 1.

Example, for today(May 06, 2013) it would be ‘2013-06-01’

B). I need to write a UPDATE query with SET date as ‘MM/01/YYYY’ format where YYYY is current year and MM is current month + 1.

Example, for today(May 06, 2013) it would be ‘06/01/2013’

C). I need to write SELECT query with WHERE clause as ‘YYYY/MM’ format where YYYY is current year and MM is current month.

Example, for today(May 06, 2013) it would be ‘2013/05’

Can anyone help me with your suggestions..

Thanks,
Surya

MuMu88
Aged Yak Warrior

549 Posts

Posted - 05/06/2013 :  11:58:27  Show Profile  Reply with Quote
Here is a way to get the dates in the format you are interested in:

--  a date in the format ‘YYYY-MM-01’ to the date field where YYYY is current year and MM is current month + 1.
SELECT STUFF(STUFF((CONVERT(CHAR(8), DATEADD(dd, 1, EOMONTH( SYSDATETIME())), 112)), 5, 0, '-'), 8, 0, '-')

-- date as ‘MM/01/YYYY’ format where YYYY is current year and MM is current month + 1.
SELECT CONVERT(CHAR(10), DATEADD(dd, 1, EOMONTH( SYSDATETIME())), 101)

-- ‘YYYY/MM’ format where YYYY is current year and MM is current month.
SELECT STUFF((CONVERT(CHAR(6), SYSDATETIME(), 112)), 5, 0, '/')


NOTE: If the current date is Dec 2, 2012 you get 2013-01-01 & 01/01/2013 as return value for the first two queries. Is this what you want?

Edited by - MuMu88 on 05/06/2013 12:06:47
Go to Top of Page

mssrone
Starting Member

2 Posts

Posted - 05/06/2013 :  13:03:21  Show Profile  Reply with Quote
I have used this logic for my UPDATE query as below..
UPDATE TBL_PURCHASE
SET PUR_DT_VAL=
(CONVERT(CHAR(10), DATEADD(dd, 1, EOMONTH( SYSDATETIME())), 101))

getting following error..

SQLERROR ON UPDATE COMMAND, PREPARE FUNCTION
RESULT OF SQL STATEMENT:
DSNT408I SQLCODE = -206, ERROR: DD IS NOT VALID IN THE CONTEXT WHERE IT IS USED
DSNT418I SQLSTATE = 42703 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXORSO SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -100 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFF9C' X'00000000' X'00000000' X'FFFFFFFF' X'000
INFORMATION


Can I use them in update and insert ?
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 05/06/2013 :  13:24:42  Show Profile  Reply with Quote
It looks like you are using DB2, the query I proposed is Microsoft SQL.
I am not familiar with DB2.
BTW this is Microsoft SQL Server Forum, you may want to post your request on a DB2 Forum.
Go to Top of Page
  Previous Topic Topic Next 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