Home » SQL Server

Always The First Day Of The Month

I have a date parameter (@rptMonth) that is selected by the user from a datepicker calendar. The date must be the first day of the month. No matter what the user selects I'd like to turn that into mm/01/yyyy. So in my query it would be something like WHERE YEAR_MONTH = DATEADD("m",datediff("m","1900-01-01",@RptMonth),"1900-01-01"),"mm/dd/yyyy" but when I try this I get incorrect syntax near ','. Don't know if this will even work. Any help is appreciated

6 Answers Found


Answer 1

Hi, Can u try the link below, it shows how to get first and last day  of the month. let me know if it works.




Answer 2

you can use a second hidden parameter  that takes the date  user selects  and converts it to the first:

=dateserial(year(parameters!rptmonth.value), month(parameters!rptmonth.value), 1)


also while im not sure of your specific requirements but in general, its more intuitive to have a month  and year selector if the day  of month doesnt matter.  Then you can convert that selection to the 1st of the month if thats what you need in your calculations.




Answer 3

Thanks for your response. I need the first day  of the month. So if the user  selects 06/22/2010, I need to turn  that into 06/01/2010.

Answer 4

Try this one.

declare @arbitraryDate datetime; 
set@arbitraryDate= getdate(); 
set@arbitraryDate= dateadd(dd, datediff(dd,0,@arbitraryDate),0)--strip time 
select dateadd(dd,-day(@arbitraryDate)+1,@arbitraryDate)--strip days 

OR select cast(convert(varchar(6),getdate(),112 + '01' as datetime)

 OR if both wont work  then this should work.

(MONTH(@pInputDate) AS VARCHAR(2))+'/01' AS DATETIME)



Answer 5

Thanks - unfortunately that didn't work. The user  select the date  from a datepicker  calendar, so it could be 06/22/2010 and I need to convert that to 06/01/2010 or the user selects  05/12/2009 and I need to convert that to 05/01/2009

Answer 6

My apologies the last one worked - Thank you Learning_SQL


I have a requirement to capture historical events in a table. Now some events will have only Year, some Year/Month, and remaining where Date is known.

We should be able to store them in the column and be able to sort etc. When publishing the information, we should be able to publish as was the input, Year only, Year-Month only, or Date.

I looked at the newer Date data type. I can insert a 4 digit year, but on retrieving it is YYYY-01-01. I do not know if it has input YYYY or YYYY-MM-DD and is indeed a Date or a Year. I am trying to avoid saving the format information in another column or something.

For sorting, when records have same Year, I would have another column to do relative sorting...

So question is - what are my best options with SQL/Entity Framework combo. And what others have done, when encountering similar - if any.

Thanks in advance.



There is a problem in Excel to program reading dates properly if they are in format “dd/mm/yyyy”.

Excel implicitly switches days and months for days = 12 or less.

Please see details on: http://www.fontstuff.com/casebook/casebook02.htm

To see it, you may try the following example:

Save dates with days = 12 or less as “dd/mm/yyyy” in .CSV (data only) file.Then record a macro when opening that file.Ensure dates appeared correctly. (use notepad)Run the recorded macro.Check days and months switched
even the format type remains the same: “*dd/mm/yyyy”.

There are already OpenText and TextToColumns methods to help.

They have FieldInfo parameter as 2-dim array:

1st element: Col#

2nd element: xlColumnDataType

xlColumnDataType is enumeration type:













MDY date

DMY date

YMD date

MYD date

DYM date

YDM date

EMD date

Skip Column

Switching xlMDYFormat and xlDMYFormat back and force does not help, neither do local settings.

To fix the problem you may use date format for example like “dd/mmm/yyyy” or run the following code:

Sub Macro1()

Workbooks.Open Filename:= "C:\Test\SwitchDayMonth.csv"

FormatGB Application.ActiveSheet.UsedRange

End Sub

Sub FormatGB(r As Range)

Dim c As Range

For Each c In r

If c.NumberFormat = "m/d/yyyy" Then c.NumberFormat = "mm/dd/yyyy"


End Sub

The code changes date format from “*dd/mm/yyyy” (Category: Date) to “mm/dd/yyyy”. (Category: Custom)

However in debug mode, you can see format “*dd/mm/yyyy” as"m/d/yyyy".

Why we have to change date format to “mm/dd/yyyy” and not to “dd/mm/yyyy”?

The answer is related to internal MS routine.

It seems because of days / months are already switched, so we just switching them back.


I have this data that has a CaseDate column that gate populated every day BY DATE, my question is how can pull and filter data by day/month/year 30 days of a particuler month in SSIS to get porocessed each month. 





' TO 31-MAY-2010]





TO 31-JUNE-2010]





TO 31-JULY-2010]



I want to get the day, month and year separately as three integers from a DateTimePicker component (Visual Studio 2005 - VB.Net).  It seems that there is no property or method that allows you to do this..am I correct or did I just miss it...

Would one need to write a custom function to obtain this then?



Hi All

i have a table like this

datetime             name      workdoneid

12-July-2010   -    john      -     100

12-July-2010   -    john      -     101

13-July-2010   -    debby     -     109

18-July-2010   -    john      -     101

10-aug-2010   -    alex      -     108

11-Sep-2010   -    john      -     100



i want the result in the following format ( per month basis)

name | total_work_done  | 1st july | 2nd july | 3rd july|----| 12th july |13th july |... 31st JULY

john   | 3                        |  0         |       0    |    0      |-----| 2          | 0            |-- 0   |


is this achievable ? Thanks in advance



Hi all,
I am using sql server 2005.
I have month in Parameter and Day of Date in parameter. Now i have to generate date from these parameter 
that is if day is 15 , month is 10 then it should be 15-10-2010.

Please suggest


 Hello all,

I have a challenge of which I can't figure out how to achieve it.

at this moment I have a gridview that can be dynamicly filled with cell's based upon the number of items selected (throug a drop down list) by a user.

the user then can enter some values in the cells to perform a bulk insert.

What I rather want is that the user selects a month, like august, and then  the gridview is filled with the amount of days from that month, so you get 31 cells in the gridview. second all the first cells should be filled with the daynumber for that day...

I hope the explanation is clear :)


Does anyone know a solution to this?






I have a table containing measurements taken every 5 minutes for a span of 8 months.  What I would like to do, is take the max measurement per day and return it.  Right now I'm using the following query which does the trick, but would involve a LOT of typing if I tried to return all the data I need:

SELECTDate, MAX(Temperature) AS Temp
FROM tblTemp
WHEREDateIN ('3/5/2010', '3/6/2010')
This returns the max temperature for 3/5/2010 and 3/6/2010 fine...  but you can see how this would be cumbersome doing it all the way through August :-)

Suppose i have a table

Appno Receive_Date Send_Date

1          9/3/2010        9/3/2010

2          9/3/2010         null      

3          9/4/2010         9/4/2010         

4          9/9/2010         9/9/2010

i m trying to run this

select * from search
where datediff(d,send_Date,getdate()<5

I have a date parameter (@rptMonth) that is selected by the user from a datepicker calendar. The date must be the first day of the month. No matter what the user selects I'd like to turn that into mm/01/yyyy. So in my query it would be something like WHERE YEAR_MONTH = DATEADD("m",datediff("m","1900-01-01",@RptMonth),"1900-01-01"),"mm/dd/yyyy" but when I try this I get incorrect syntax near ','. Don't know if this will even work. Any help is appreciated


Hi. I'm using Sharepoint 2007 and we're experiencing a strange issue. In the Month view, if I look at the month of September, September 30th appears to have 15 items.  Clicking on the date displays these 15 items, but there should be considerably more. The entire day's schedule stops at 6:30 am. If I view the month of October, and look at the September 30th date, it indicates that there are 36 items, which is the correct number.  This pattern happens regardless of which month I'm looking at. It's always the last day of the month affected. The number of missing items varies from month to month, generally around 20 items. I'm baffled, and haven't got a clue how to attempt to troubleshoot this issue.

I would greatly appreciate any suggestions on how to attempt to resolve this!



I have 2 dates (in the form MM/DD/yyyy) representing the start date of the employee and the end date of the fiscal year:

the employee started working on 12/15/2010 and the fiscal year ends on 08/31/2011

I want to calculate the number of months that the employee worked between those 2 dates

in reality this employee will have been working until 08/31/2011 for 8.5 months

datediff function returns 8

how to calculate exact number of months in sql?

thank you in advance


Ihave a table that has a day col, month col, and year col.

Day  Month  Year
1         10     2009
1         9       2008
30       10     2010


How can I cast or convert each row into one "datetime" value and format it like mm/dd/yyyy.  I need to do so to have in a where clause where datetime >= @ fromdate and datetime <= @todate


I'm trying to setup a Calendar view for a list in SharePoint 2010. Everything is working the way I want, except there are no icons to change the view from Month, Week, or Day. Right now, if someone clicks on a date on the calendar, it switches to the Day view. But there is no way for them to go back to the month view.

I open the page in SharePoint Designer, and in the Design view I can see three icons on the Calendar (Month, Week, Day). But at no time do these icons show up anywhere except the design view.

I want it to have the arrow buttons, the month and year (which are already displaying) and then the icons to change the view. How do I get them to appear?

<script type="text/javascript" language="javascript">
function testDate(value)

// var sel = document.forms(0).DDAY;
var sel =document.getElementById('DDAY');

sel.options.length = 0;

var NoOfDays = DaysInMonth(value);

for(var i=1;i<=NoOfDays;i++)
//create new option
var option = new Option(i, "Value" + i);
sel.options(i) = option;


<select onchange="testDate(this.value)" id="MMONTH" style="display: none;
z-index: 101; left: 0px; width: 80px; position: absolute; top: 2px" runat="server">
<option selected="selected" value="01">JAN</option>
<option value="02">FEB</option>
<option value="03">MAR</option>
<option value="04">APR</option>
<option value="05">MAY</option>
<option value="06">JUN</option>
<option value="07">JUL</option>
<option value="08">AUG</option>
<option value="09">SEP</option>
<option value="10">OCT</option>
<option value="11">NOV</option>
<option value="12">DEC</option>

<select id="DDAY" style="display: none; z-index: 101;
left: 0px; width: 50px; position: absolute; top: 2px" runat="server">
<option selected="selected">1</option>


i have the above code
need to populate day dropdown on month change



i think u got me wrong

i dont want the month , i need the days in the month

i have 2 dropdown

drpdays & drpmonth

drpmonth is harcoded as shown above on the cleint side
wht i need is on the chnage in drpmonth the drpday dropdown shld be automatically filled with the  nos of days of that particular month for the current year

eg current year 2010 if anybody selects FEB n drp month the drpday shld be populated with 1-28
when the user selects a day from the daydrpdown  the selected values shld be assigned to a variable

It took me hours to write this code and once I test it. I get nothing, no errors, nada. I am new to C#, but I thought I did it well. I am able to enter the number then nothing happens. When I press the button to display the error box, it does not work. l have gotten (35/50) for my grade, but I at lease want it to get it working. What did I do wrong?
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace WindowsFormsApplication1
    public partial class Form1 : Form
        public Form1()

        private void button1_Click(object sender, EventArgs e)
            int monthNo = 0;
            string[] month = new string[13];
            bool flag = true;
            month[0] = "\0";
            month[1] = "January"; month[2] = "February"; month[3] = "March";
            month[4] = "April"; month[5] = "May"; month[6] = "June";
            month[7] = "July"; month[8] = "August"; month[9] = "September";
            month[10] = "October"; month[11] = "November"; month[12] = "December";
                monthNo = int.Parse(textBox1.Text);
                if (monthNo <= 0 || monthNo > 12)
                    MessageBox.Show("Invalid Input!",
                        "Must be 1 to 12!");
                    switch (monthNo)
                        case 1:
                        case 3:
                        case 5:
                        case 7:
                        case 8:
                    case 10:
                        case 12:
                            textBox2.Text = "31 days";
                        case 4:
                        case 6:
                        case 9:
                        case 11:
                            textBox2.Text = "30 days";
                        case 2:
                            textBox2.Text = "28 days";
            while (flag);




How do I get my Datetime out in 3 dropdownlists :

ddlDay = Day

ddlMonth = Month

ddlYear = Year




I am having a problem with a function in SQL.

I have a calendar, and you can create an event to be shown on it. In the create event page, I can select a radio button if I want to have this event reoccur every year. So I have a drop down with (first, second, third, fourth, last) and then a drop down with (Monday through Sunday listed).

The problem I am having is, some months have for say 4 Mondays or 5 Saturdays. But if I select to have my event occur, on the LAST SATURDAY of MAY. There are only 4 Saturdays in that month. So yeah I should have selected FOURTH, NOT LAST.  But I want my code to have the ability to see there are only four Saturdays and have the LAST feature be the fourth week not the fifth.

This isn't the entire function but this is where I need the change and your help!

I have this right now:


SET @dayCount = LTRIM(RTRIM(@typeID))
			IF @dayCount = 'first'BEGINSET @nOccur = 1
				ENDElseIF @dayCount = 'second'BEGINSET @nOccur = 2
				ENDELSEIF @dayCount = 'third'BEGINSET @nOccur = 3
				ENDELSEIF @dayCount = 'fourth'BEGINSET @nOccur = 4
				ENDELSEIF @dayCount = 'last'BEGINSET @nOccur = 5					
				ENDELSEBEGINSET @nOccur = -1


Thanks in advance!


Hi friends,

suppose my current Date is 01/11/2008. so in this case how to get previous month Last day and Previous month  first Day ?

Pls provide me suggestions.



<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure