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
 General SQL Server Forums
 New to SQL Server Programming
 Help needed in Inserting/Updating specific dates
 New Topic  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

547 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

547 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  
 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.09 seconds. Powered By: Snitz Forums 2000