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.
| Author |
Topic |
|
mssrone
Starting Member
2 Posts |
 Posted - 2013-05-06 : 11:16:08
|
| 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 - 2013-05-06 : 11:58:27
|
| Here is a way to get the dates in the format you are interested in:[CODE]-- 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, '/')[/CODE]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? |
 |
|
|
mssrone
Starting Member
2 Posts |
Posted - 2013-05-06 : 13:03:21
|
| 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 ? |
|
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-05-06 : 13:24:42
|
| 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. |
|
|
|
|
|
|
|
|
Help needed in Inserting/Updating specific dates