Easiest query to find start of month

Question

Easiest query to find start of month

brij_rauthan

SSCommitted

Points: 1521
More actions
July 4, 2006 at 2:56 am

#168823

Hi Guys
What will be easiest way without using variables to get first date of month from a date.
I got someting like:
Select Convert(Datetime, Cast(DatePart(mm,GetDATE()) as
Varchar) + '/1/' + Cast(DatePart(yy,GetDATE()) as Varchar),101)
Does anybody have something better?
Brij

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Brij

Try this. It will return a date whose time component is the same as the date you specify. If you want it to return a date that ends with 00:00:00.000 then you can apply a similar logic to the time component.

select dateadd (d, 1 - datepart(d, getdate()), getdate())

John

brij_rauthan SSCommitted Points: 1521 More actions · Answer 2

Thanks John

Yes that is correct I need time component as 00:00:00.000

Brij

stax68 SSChampion Points: 11711 More actions · Answer 3

select

dateadd (month, datediff(month, 0, getdate()), 0)

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 4

Is that automatically color-coded or you do do it by hand?

I'd like to know, because some examples I give here needs color-coding.

N 56°04'39.16"
E 12°55'05.25"

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 5

use SQL2005 SSMS and you'll find it very usefull.

copy/past keeps the colors

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

stax68 SSChampion Points: 11711 More actions · Answer 6

Yes that's it! You still have to format as definition term, courier new, size 2 though.

And of course, always <select all>,<copy> before clicking the button, in case of session timeout!

But I'm teaching my grandmother to suck eggs now.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

Is she good at it?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Factor SSC-Insane Points: 20244 More actions · Answer 8

The last time I had to do it, I actually blew the egg rather than sucked it. It was a swan's egg that was highly addled. One drills a hole at both ends and blows from the top end. Had I sucked it, I'd have had a mouth-full of addled egg. We really need the advice of an egg-collector.

SELECT CONVERT(DateTime,'01 '+SUBSTRING(CONVERT(CHAR(11), GETDATE(),113),4,8))

Best wishes,
Phil Factor

stax68 SSChampion Points: 11711 More actions · Answer 9

I have advice for egg collectors. Stop killing off rare species.

For those who don't know, the saying is an old one, and refers to sucking eggs for food, a basic skill which (presumably) any grandmother could be expected to have already acquired.

My own grandmothers as far as I know eat their eggs (yes, free-range and organic) in the form of an omelette. I suppose they could blow the eggs in order to do so, thus disproving another old saying...

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant