Skip to content
Fix Code Error

How can I select the first day of a month in SQL?

March 13, 2021 by Code Error
Posted By: Anonymous

I just need to select the first day of the month of a given datetime variable.

I know it’s quite easy to do using this kind of code:

select CAST(CAST(YEAR(@mydate) AS VARCHAR(4)) 
+ '/' + CAST(MONTH(@mydate) AS VARCHAR(2)) + '/01' AS DATETIME)

But this is not very elegant, and probably not very fast either.

Is there a better way to do this? I’m using SQL Server 2008.

Solution

SELECT DATEADD(month, DATEDIFF(month, 0, @mydate), 0) AS StartOfMonth
Answered By: Anonymous

Related Articles

  • Can't find why this datetime test fails, in F#
  • How to return only the Date from a SQL Server…
  • Obtain most recent value for based on index in a…
  • How to get the first and last date of the current year?
  • SQL Server Group By Month
  • Calculate time difference in minutes in SQL Server
  • T-SQL datetime rounded to nearest minute and nearest…
  • Log record changes in SQL server in an audit table
  • Get first day of week in SQL Server
  • AppCompat v7 r21 returning error in values.xml?
  • Confused about the Visitor Design Pattern
  • Best approach to remove time part of datetime in SQL Server
  • How to calculate age (in years) based on Date of…
  • MS SQL Date Only Without Time
  • SMTP error 554
  • Java string to date conversion
  • Meaning of @classmethod and @staticmethod for beginner?
  • How can I truncate a datetime in SQL Server?
  • Javascript add leading zeroes to date
  • How to print a date in a regular format?
  • Data structure for maintaining tabular data in memory?
  • Converting between datetime, Timestamp and datetime64
  • How to increment datetime by custom months in python…
  • How to calculate age in T-SQL with years, months, and days
  • SQL statement to select all rows from previous day
  • Conversion failed when converting the varchar value…
  • get UTC timestamp in python with datetime
  • How do I get just the date when using MSSQL GetDate()?
  • How to add "new" element/objects s to a list in javascript?
  • How to get min max day in a week every month?
  • How to get week number of the month from the date in…
  • polymer 1.0 dom-repeat filter only runs once
  • Best way to do multi-row insert in Oracle?
  • Calculating a number from items in an array then…
  • How to group by month from Date field using sql
  • SQL NVARCHAR and VARCHAR Limits
  • Subtract one day from datetime
  • Filtering an object based on key, then constructing…
  • How to obtain the start time and end time of a day?
  • biblatex: splitting bibliography entry which are…
  • Using VBA unable to get the difference between two…
  • PostgreSQL: days/months/years between two dates
  • Webpack build error
  • type object 'datetime.datetime' has no attribute 'datetime'
  • Declare a variable which converts Date data type to Integer
  • Persistent invalid graphics state error when using ggplot2
  • Ember.Object computed property returning an object
  • How to query for Xml values and attributes from…
  • How to determine the number of days in a month in…
  • Struggling with a nested where, group by and min/max in SQL
  • A SELECT statement that assigns a value to a…
  • Difference between System.DateTime.Now and…
  • How to compare two dates to find time difference in…
  • Polymer nested dom-repeat templates are not updating…
  • Aurelia, navigate is not updating viewport
  • How to query DATETIME field using only date in…
  • Maximum size of a varchar(max) variable
  • data.table vs dplyr: can one do something well the…
  • python pandas extract year from datetime: df['year']…
  • java.util.Date format conversion yyyy-mm-dd to mm-dd-yyyy
  • SQL Server: Query fast, but slow from procedure
  • Convert Year/Month/Day to Day of Year in Python
  • How do I simplify Laravel query for date searching…
  • SQL grouping by month and year
  • Don't let the user select a past date?
  • How to connect to local instance of SQL Server 2008 Express
  • Binding ComboBox SelectedItem using MVVM
  • Get the last day of the month in SQL
  • Subtract three rows from an array in angular 7
  • Is resource nesting the only way to enable multiple…
  • Generate SQL Create Scripts for existing tables with Query
  • How do I convert a date/time to epoch time (unix…
  • How to add time to DateTime in SQL
  • How to truncate milliseconds off of a .NET DateTime
  • No function matches the given name and argument types
  • Why does git perform fast-forward merges by default?
  • NSDate get year/month/day
  • Best way to find the months between two dates
  • Output not incrementing correctly - C++
  • SQL Query to find the last day of the month
  • Best practices for SQL varchar column length
  • How can I create an array from number and add some…
  • Binding an ember model's date attribute to…
  • Conversion of a varchar data type to a datetime data…
  • "import datetime" v.s. "from datetime import datetime"
  • SQL Transaction Error: The current transaction…
  • Creating list with the same number of values
  • Using StringWriter for XML Serialization
  • Javascript, Time and Date: Getting the current…
  • Use of Jquery on scroll event
  • How can I check if a month has 31 days or not
  • Drop all the tables, stored procedures, triggers,…
  • DatePicker onDateSet method not running
  • Tkinter Duration Timer—What method will allow this…
  • Create array of objects based on length; each object…
  • Scripting Required for Polymer Submenu/Pages Navigation?
  • How can I compare time in SQL Server?
  • How to auto insert the current user into my db when…
  • Using DateTime in a SqlParameter for Stored…
  • Calculate last day of month in JavaScript

Disclaimer: This content is shared under creative common license cc-by-sa 3.0. It is generated from StackExchange Website Network.

Post navigation

Previous Post:

How to get line count of a large file cheaply in Python?

Next Post:

Oracle SELECT TOP 10 records

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

.net ajax android angular arrays aurelia backbone.js bash c++ css dataframe ember-data ember.js excel git html ios java javascript jquery json laravel linux list mysql next.js node.js pandas php polymer polymer-1.0 python python-3.x r reactjs regex sql sql-server string svelte typescript vue-component vue.js vuejs2 vuetify.js

  • you shouldn’t need to use z-index
  • No column in target database, but getting “The schema update is terminating because data loss might occur”
  • Angular – expected call-signature: ‘changePassword’ to have a typedeftslint(typedef)
  • trying to implement NativeAdFactory imports deprecated method by default in flutter java project
  • What should I use to get an attribute out of my foreign table in Laravel?
© 2022 Fix Code Error