date problem

Can someone tell me what is wrong with this statement:

case when DatePart('day',getDate())='Monday' then DateAdd('day',-3,getDate()) else DateAdd('day',-1,getDate())

The date part function will return an INT for the day passed in not Mon, Tues, Wed..ect.

SELECTDATEPART(DAY,GETDATE()) = 'Monday' will be invalid because you are comparing an int to a string and it will fail conversion.

Example SELECT DATEPART(DAY,'6/22/2007') will return 22.

|||

use the following expression,

CaseWhendatename(w,getdate())='Monday'ThenDateAdd('day',-3,getDate())elseDateAdd('day',-1,getDate())

|||

or this...

this will work

Code Snippet

select

casewhenDatePart(dw,getDate())= 1

thenDateAdd(d,-3,getDate())

elseDateAdd(d,-1,getDate())end

|||

or this...

this will work

... whenDatePart(dw,getDate())= 1

As long as DATEFIRST has been set to Monday instead of the US default setting of Sunday (which makes Monday = 2) ...

And of course, it may work just fine and then someday someone changes the DATEFIRST setting.

|||Execellent choice Mani -your suggestion isn't dependent upon the DATEFIRST setting...sql

date part of date time

SELECT ltrim(str(datepart(yyyy,getdate()))) +'-'+
replicate('0',2-len(ltrim(str(datepart(mm,getdate())))))+ltrim(str (datepart(mm,getdate())))+'-' +replicate('0',2-len(ltrim(str(datepart(dd,getdate())))))
+ltrim(str(datepart(dd,getdate())))

This is how i am getting datepart of datetime.Is there any other way to get the date and also time seperately..

Thanks.Convert(char(10), getdate(), 120)|||SELECT CONVERT(varchar(10),GetDate(),101), CONVERT(varchar(10),GetDate(),108)|||Beat you by one second, Kaiser! Nyah nyah nyah! Too slow! Ya snooze, ya looze! :p :p :p :p :p|||SELECT CONVERT(varchar(10),GetDate(),101), CONVERT(varchar(10),GetDate(),108)

Where should i know to use 101 and 108 in convert functions.I could get that from BOl...
Can u help me with that.

Thanks and sorry for asking such a silly question.|||I'm sorry i got that myself.

Thank u all.|||I'm sorry i got that myself.

Thank u all.

Congratulations! Because of forum a lot of people became so lazy but not you.|||Beat you by one second, Kaiser! Nyah nyah nyah! Too slow! Ya snooze, ya looze! :p :p :p :p :p

Yeah...see what happens when work gets in the way...

I'm giving out specs over the phone to a noob while I'm doing this...

And this ain't sqlteam...that's 1 minute increments

Damn...[sniped] anyway

And what's with 120?

bleeeeeach...

:D|||And what's with 120?Wah, wah, waaaaah!

120 is just the "junior grade" version of 121, my preferred style!

-PatP|||121? How pretentious. 120 shines in its simplicity and elegance.

Dude, 121 is SOOOOO last version.....

And what's with the varchar, eh?|||Personally I like 126..talk about pretentions...but that's just my mainframe db2 talking...

too bad they lost pos 4-6 of their milliseconds...gotta be the cpu's fault..right?

Figured he wanted 101 though..nice and pretty...

And geez...I need to speak with Karolyn or ms_sql_dba to straighten you out?

:D

I saw you suck'n up blind dude... :)

Varchar...force of habit...

we need more banter in the corral...

http://www.dbforums.com/showthread.php?p=3672844#post3672844|||Don't threaten to sick your dogs on me!

Like I'm gonna be scared of a "126"er...|||Dude, 121 is SOOOOO last version.....Oh, they used to say that about my Leisure Suit and silver tipped cowboy boots too!

-PatP|||Hold on to those. They're coming back.|||Hold on to those. They're coming back.

I hated the 70's..

What do you guys think about this:

http://www.dbforums.com/t996699.html

Date parameter works in report designer but not when deployed.

I use the following expression as the default parameter for a report
called 'Start':
=IIF(DATEPART("d",today()) >= "12",DateSerial(Year(today()),
Month(today()), 1) ,DateSerial(Year(today()), Month(now)-1, 1))
The query then uses the parameter to filter the data so I only get the
transactions I want depending on what day of the month it is 'today',
i.e., 'Date >= @.start'
This works fine in Report Designer in VS. However, it doesn't work once
the report is deployed and I run it through Report Manager - I get all
the transactions.
Anyone know why this is happening? I'm still using SQL2000I see two problems with your expression:
1) DatePart returns an integer not a string
2) With DateSerial(Year(today()), Month(now)-1, 1) what happens when now is
January? What Month is January minus 1?
I suggest you use the following expression:
=Iif( DatePart(DateInterval.Day, Today()>=12, DateSerial(Year(Today()),
Month(Today()),1), DateAdd(DateInterval.Month, -1, DateSerial(Year(Today()),
Month(Today()),1)))
HTH
--
Magendo_man
Freelance SQL Reporting Services developer
Stirling, Scotland
"paddydog" wrote:
> I use the following expression as the default parameter for a report
> called 'Start':
> =IIF(DATEPART("d",today()) >= "12",DateSerial(Year(today()),
> Month(today()), 1) ,DateSerial(Year(today()), Month(now)-1, 1))
> The query then uses the parameter to filter the data so I only get the
> transactions I want depending on what day of the month it is 'today',
> i.e., 'Date >= @.start'
> This works fine in Report Designer in VS. However, it doesn't work once
> the report is deployed and I run it through Report Manager - I get all
> the transactions.
> Anyone know why this is happening? I'm still using SQL2000
>

Date Format Question

Hi all - I have a question regarding formating getdate(). Below is my query.

SELECT

cast(datepart(yyyy, getdate()) as char(4))

+'-'+ cast(datepart(mm,getdate()) as char(2))

+'-'+ cast(datepart(dd, getdate()) as char(2))

Result

2007-5 -30

The result I would like is:

2007-05-30

Can anyone help me with this?

Thanks in Advance.

You could use:

Code Snippet

SELECT convert( varchar(10), getdate(), 120 )

-
2007-05-30

Refer to Books Online, Topic: 'Cast and Convert' for the usage of the 'style' codes (the 120 above).

Date Format MM/YYYY

Hi:
I need to format a date like this:
01/2004 -> 'MM/YYYY'
I know that I can do this...
SELECT CAST(datepart(mm,getdate()) AS VARCHAR) + '/' + CAST(datepart(yyyy,getdate()) AS VARCHAR)
but , Is there is another better solution?select right(convert(char(10), getdate(), 103), 7)|||Originally posted by joejcheng
select right(convert(char(10), getdate(), 103), 7)

Thanx a lot :D.
You save my life !!