SQL Query QUESTION - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

SQL Query QUESTION

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-11-20 : 07:21:59

Al writes "Hi all. I know the basics a bit, but learning by fire. lol Anyway I hope some guru's can help me. Here is what they want me to create, but I have NO clue where to even start. I saw something like SET DATEFORMAT, but I have none the idea how to use it, or if that's even what I need. PLEASE HELP. Thank the guru's in advance. The spec is below:

This interface service describes how to populate the EFFDT field in the Project_7 table for a GL record.
G01.Acc_Eff_DT
G01
1. If the code is being run for the first time (see Interface Service: Load GL Records) return ‘2006-12-21’.
2. Else if the current day of the week is Thursday return the date in the format of ‘YYYY-MM-DD’.
3. Else return the date of the next Thursday in the format of ‘YYYY-MM-DD’."

Antonio
Posting Yak Master

168 Posts

Posted - 2006-11-20 : 08:15:06

quote:
Originally posted by AskSQLTeam

Al writes "Hi all. I know the basics a bit, but learning by fire. lol Anyway I hope some guru's can help me. Here is what they want me to create, but I have NO clue where to even start. I saw something like SET DATEFORMAT, but I have none the idea how to use it, or if that's even what I need. PLEASE HELP. Thank the guru's in advance. The spec is below:

This interface service describes how to populate the EFFDT field in the Project_7 table for a GL record.
G01.Acc_Eff_DT
G01
1. If the code is being run for the first time (see Interface Service: Load GL Records) return ‘2006-12-21’.
2. Else if the current day of the week is Thursday return the date in the format of ‘YYYY-MM-DD’.
3. Else return the date of the next Thursday in the format of ‘YYYY-MM-DD’."

I suggest you use Books On Line and search date and time functions in SQL. I am sure a combination of these functions will get you the answer you require.

DATEADD
DATEDIFF
DATENAME
DATEPART
GETDATE

...etc etc...

_________________________________________________________________________________________________________________________
Inability is a disaster; patience is bravery; abstinence is a treasure, self-restraint is a shield; and the best companion is submission to Divine Will.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-20 : 11:26:36

Try this

UPDATE Project_7
SET EFFDT = CASE 
WHEN EFFDT IS NULL THEN '2006-12-21'
WHEN datepart(dw, EFFDT) = 1 THEN convert(varchar(10), dateadd(dd, 4, EFFDT), 120)
WHEN datepart(dw, EFFDT) = 2 THEN convert(varchar(10), dateadd(dd, 3, EFFDT), 120)
WHEN datepart(dw, EFFDT) = 3 THEN convert(varchar(10), dateadd(dd, 2, EFFDT), 120)
WHEN datepart(dw, EFFDT) = 4 THEN convert(varchar(10), dateadd(dd, 1, EFFDT), 120)
WHEN datepart(dw, EFFDT) = 5 THEN convert(varchar(10), EFFDT, 120)
WHEN datepart(dw, EFFDT) = 6 THEN convert(varchar(10), dateadd(dd, 6, EFFDT), 120)
WHEN datepart(dw, EFFDT) = 7 THEN convert(varchar(10), dateadd(dd, 5, EFFDT), 120)
END

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-21 : 11:38:48

quote:
Originally posted by AskSQLTeam

Al writes "Hi all. I know the basics a bit, but learning by fire. lol Anyway I hope some guru's can help me. Here is what they want me to create, but I have NO clue where to even start. I saw something like SET DATEFORMAT, but I have none the idea how to use it, or if that's even what I need. PLEASE HELP. Thank the guru's in advance. The spec is below:

This interface service describes how to populate the EFFDT field in the Project_7 table for a GL record.
G01.Acc_Eff_DT
G01
1. If the code is being run for the first time (see Interface Service: Load GL Records) return ‘2006-12-21’.
2. Else if the current day of the week is Thursday return the date in the format of ‘YYYY-MM-DD’.
3. Else return the date of the next Thursday in the format of ‘YYYY-MM-DD’."

You should format dates in the front end application(if you use)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources