changing a date

[hawley]

Is it possible to convert a date 05/15/2004 to 05/01/2004 in a select statement?

Thanks in advance!!!

 

[cLFlaVA]

Could you go into some more detail? Do you want any date to go to 05/01/2004? If that's the case you should just select '05/01'2004'. Do you want to subtract 15 days? Do you want to get the first date of the month, whatever the month is? Do you want to update data in the database?

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[hmckillop]

to subtract dates use DateAdd function, i.e.
to subtract 15 days use
SELECT DATEADD(dd, -15, MyDateField)

to find the first of the month use
SELECT '01' + '/' + CONVERT(VARCHAR, DATEPART(mm, MyDateField)) + '/' + CONVET(VARCHAR,DatePart(yyyy, MyDateField)))




"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.

 

[hawley]

Actually what I want to happen is no matter what the date is I want it to go to the first of the month. The date will always be something different. Examples.
08/04/04 -> 08/01/04
10/26/04 -> 10/01/04
04/30/04 -> 04/01/04

 

[ESquared]

This question has been asked so many times lately that it seems I should write an FAQ on it.

To get the first day of the month, you can do

[tt]SELECT Convert(datetime,Convert(char(6),TheDate,112) + '01')[/tt]

You can leave the outer datetime conversion off if the context of the query will implicitly convert it.