Just a week ago, my Database Team member asked me what is the best way to only select date part from datetime. When ran following command it also provide the time along with the date.
SELECT GETDATE()
ResultSet: 2007-06-10 7:00:56.107
The required outcome was only 2007/06/10.
I asked him to come up with solution by using date functions. The method he suggested was to use
SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))
I approved his method though, I finally suggested my method using function CONVERT.
SELECT CONVERT(VARCHAR(10),GETDATE(),111)
The reason I use this because it is very convenient as well as provides quick support to convert the date in any format. The table which suggests many formats are displayed on MSDN.
Some claims that using CONVERT is slower than using DATE functions, but it is extremely negligible. I prefer to use CONVERT.
Here is the video discussing the same concepts.
Let me know what is your favorite method to select only date part from datetime. I would like to know what are the different methods and if any other method is interesting I will be happy to blog about that in the future with due credit.
Here is Part 2 of the blog post: SQL SERVER – Retrieve – Select Only Date Part From DateTime – Best Practice – Part 2.
In SQL Server 2008 we also have a new datatype of Date and Time, which can be very handy if you are using SQL Server 2008 or later versions. You can read about that in this blog post SQL SERVER – DATE and TIME in SQL Server 2008.
Reference: Pinal Dave (https://blog.sqlauthority.com)
405 Comments. Leave new
I need a lill help from you all smartys!
I am working on an update of a datetime field and receiving the following error.
Query:
Update tbl
SET Month(column 1) = Month(Column 2),
Day(column 1) = Day(column 2)
WHERE Month(column 1) = Day(column 2)
and Day(column 1) = Month(column 2)
and id in (111,222,333)
Error:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘)’.
Please assist.
Thanks, — imransi —
The above statement is correct. Are you sure this is the code that you executed?
Hi sir,
Is there any way to ensure that the date column is in given format
1 Always use proper DATETIME datatype store dates
2 It is the job of front end application to do formation
of Course this was a good article, but still didn’t get my solution.
i have a date column on my table and i need to assure that all these date are in same formate… just verify, make sure.
ex. all are in MM-DD-YYYY just match formate
As long as you express dates in unambigous formats like YYYYMMDD format, you dont need to worry about how dates are stored in datetime column
my data is correct and isdate returns 0(zero), still i need to check they are in all correct format i.e for my system 2010-7-6 is right where as 2010/7/6 is wrong format.
Make use of set dateformat
set dateformat ymd
–your query
hello,
my
query is
select start_date from employee where emp_Id=”101″;
how to get date only part in database to my textbox display
If you use front end application, use format function there
ex
in VB6,
format(date,”DD/MM/YYYY”)
Hi ,
pls help
how to select the data between May-2000 to March-2010
i tried below query but not working,
select * from tbl_candidate where
(Datename(Month,TestDate) + ” + Datename(Year,TestDate)) between ‘May-2000’
and ‘March-2010’
pls help..
Try this code
select * from tbl_candidate
where
TestDate>=cast(replace(‘May-2000′,’-‘,’ ‘) as datetime) and
TestDate<dateadd(month,1,cast(replace('March-2010','-',' ') as datetime))
Thanks a lot madhivanan,its working fine
Even better way:
CONVERT(DATETIME,Convert(VARCHAR(8),DATE,112), 112)
Actually the better way is
dateadd(day,datediff(day,0,date),0)
Hi…..
This is very useful to all who are all work in IT field….
Great Article….!!!!!!!!!
Hi,
I want to add Min column in Stored procedure .
ex – Mins(column)
3:10
4:20
5:10
2:30
Output should be 15:10
select sum(col1), min(col2) from table
Hello,
This might be a bit off topic but here goes:
I have the following query which is extracting and counting the day/s of the month from my table whenever a transaction takes place – the column is called incidentdate.
This is working fine but I also need to include in the result of the query days of the month where a transaction did NOT take place, those days should show as zero when counted.. following is my query and the results with the missing days:
‘SELECT day(incidentdate) as DayOfMonth, count(day(incidentdate)) as IncidentCount FROM phl_csapp GROUP BY day(incidentdate);’
DaysOfMonth IncidentCount
2 6
3 6
4 4
5 1
” ”
Problem – the 1st day of the month is missing in the result of the query because I have no transaction (incident) for that day. How do I include the days that I don’t have a transaction to display a zero instead of simply not showing when the query is run……….
I’ve been banging away at this for 4 days now :)
ramdas
Maybe I am not reading it correctly or understanding the date set. In your dataset, is there a column for incident?
I am picturing a column called incidentdate and each time an incident occurs, you are adding the date of occurrence. If this is the only way you are logging it, the best approach would probably be on the front end.
If you absolutely need it in SQL, you will need to loop through the days in the month and select the count for each one. Doing this will create a separate select for each day so, if you want it all in one result table, a temp table storing the count per day would probably be best. Something like:
declare @day int
declare @days int
declare @temp table( DayOfMonth int, IncidentCount int)
select @days = DAY(dateadd(mm, datediff(mm, -1, GETDATE()), -1))
set @day = 1
while @day <= @days
begin
insert into @temp select @day, count(DAY(incidentdate)) from phl_csapp
where DAY(incidentdate) = @day
set @day = @day + 1
end
select * from @temp
This is taking the days of the current month and you could always just make it loop 1 – 31 and eliminate the days piece.
Just my .02.
Incidentally, I did some testing with the convert vs dateadd method and I will back that, though the convert method is comparable performance wise with a small amount of data, it does not scale well.
I am pulling reporting for thousands of data rows and the convert method will increase time to a second or two while the dateadd method will return under a second. When building out a report via a web interface, 1-2 seconds is very impacting when it’s in the back end and you still have a web front end to wait on.
I would say the convert method is great for ease of use on small data sets, but stick to the dateadd method if you have a larger dataset you need to perform the query on.
Yes. It is because the convert does convertion to another datatype whereas dateadd and datediff wont
Stored Procedures: SQL Server 2008:: Visual Studio 2010
Do variables pass from a VB.NET page to a Stored Procedure when you are using a master page?
I have copied the code back and forwards and used the same code / Stored Procedure programme.
I have a table named Orders and the o nly way I seem to be able to select orders by OrderDate is by using a SP. I use a Calendar to collect the FromDate and another to collect the ToDate and put this into a textbox (tbFromDate, tbToDate) (I can’t collect directly from a Calendar nor a label, just a textbox. I have a button to run the Stored Procedure and put the output into a datagrid (dgA)
——————————
Protected Sub ButtLoadData_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ButtLoadData.Click
If IsPostBack Then
Dim DBConn As SqlConnection
DBConn = New SqlConnection(“Server=localhost;Initial Catalog=OLB;” _
& “Integrated Security=SSPI”)
Dim cmd As SqlCommand = New SqlCommand(“Orders1”, DBConn) ‘Do not need EXEC
cmd.CommandType = CommandType.StoredProcedure ‘ New line added
Dim pFromDate As SqlParameter = New SqlParameter(“@FromDate”, SqlDbType.Date, 10)
pFromDate.Value = Request(“tbFromDate”)
cmd.Parameters.Add(pFromDate)
Dim pToDate As SqlParameter = New SqlParameter(“@ToDate”, SqlDbType.Date, 10)
pToDate.Value = Request(“tbToDate”)
cmd.Parameters.Add(pToDate)
DBConn.Open()
cmd.ExecuteNonQuery()
Dim rwReader As SqlDataReader = cmd.ExecuteReader
dgA.DataSource = rwReader
dgA.DataBind()
DBConn.Close()
rwReader.Close()
End If
End Sub
———————————–
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author: Caz
— Create date: 22/09/2010
— Description: Orders between dates
— =============================================
CREATE PROCEDURE Orders1
— Add the parameters for the stored procedure here
@FromDate date,
@ToDate date
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
— Insert statements for procedure here
SELECT
SuppName,
Orderid,
OrderDate,
CP,
VAT,
Invoice
from Orders
where
OrderDate >= @FromDate
and OrderDate <= @ToDate
order by OrderDate
END
GO
——————————
Am I missing something here or just being stupid?
Any help gratefully received.
Thanks
John
I haven’t encountered any errors when performing similar tasks from either masterpage or template. Are you getting the actual param value? I created a similar approach using my DB and SP on my VB.Net page but I am passing the value through directly. I may be a bit rusty but shouldn’t you specify whether the request object is from a QueryString or Form?
Anyhow, my test on my site was as such. Hopefully this helps somehow. It gave me something to do while I worked out my own SP issue. ^_^
Also, Request.Form could easily be changed to Request.QueryString(“fieldName”) if it’s value through QS or, if an ASP server control TextBox with id “toDate” you could do toDate.Text. Anyways, this is what worked for me.
——————
Dim dbcon As New SqlConnection(“Server=localhost;database=siteDb;Integrated Security=True;”)
Dim dbcmd As New SqlCommand(“getUsers”, dbcon)
dbcmd.CommandType = CommandType.StoredProcedure
Dim ut As Integer = 0, gi As Integer = 0
If (Not Request.Form(“uType”) Is Nothing) Then
Integer.TryParse(Request.Form(“uType”), ut)
End If
If (Not Request.Form(“gId”) Is Nothing) Then
Integer.TryParse(Request.Form(“gId”), gi)
End If
dbcmd.Parameters.Add(New SqlParameter(“@userType”, ut))
dbcmd.Parameters.Add(New SqlParameter(“@groupId”, gi))
Try
dbcon.Open()
Dim dGrid As New DataGrid()
dGrid.DataSource = dbcmd.ExecuteReader()
dGrid.DataBind()
Me.Page.Controls.Add(dGrid)
Catch ex As Exception
Response.Write(ex.Message)
Finally
dbcmd.Dispose()
dbcon.Close()
End Try
Thank you for your help. However I am still having issues.
I am using this to transfer teh data: I know you say something else above but I am not that clever at translating it:
—————
Dim pOrderID As SqlParameter = New SqlParameter(“@OrderID”, SqlDbType.NVarChar, 12)
pOrderID.Value = Request(“tbOrderID”)
cmd1.Parameters.Add(pOrderID)
—————-
I am getting the value from a select column in another datagrid (dgA)
—————-
Protected Sub dgA_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles dgA.SelectedIndexChanged
tbOrderID.Text = “40” ‘dgA.SelectedItem.Cells(1).Text
LoaddgB()
End Sub
——————-
I have changed the variable to an actual “40” and there is a record with OrderID = 40 as an Int in the db.
I get the response
Procedure or function ‘OrdersNonStock4’ expects parameter ‘@OrderID’, which was not supplied.
————————–
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author: Caz
— Create date: 23/09/2010
— Description: Order Totals for Non Stock
— =============================================
CREATE PROCEDURE OrdersNonStock4
— Add the parameters for the stored procedure here
@OrderID nvarchar
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
— Insert statements for procedure here
SELECT
SUM(CP) as SumCP,
SUM(Delivery) as SumDelivery,
SUM(VAT) as SumVAT,
SUM(Duty) as SumDuty,
SUM(Invoice) as SumInvoices
from Orders
Where OrderID = @OrderID
END
GO
——————–
I’m sorry to be a nuisance but all I am trying to do is select a number of orders held in one datagrid, select one and have the total of the items displayed in another grid. I can then take those totals and update the record with the latest totals.
I guess I ned to go back to school but at my age I’m too old! :-)
Thank you
John
Hi ,
I need to resolve a query wherein I have to display the names of customers who have the same date of birth
CLAUSE: be sure they are not matched against their own record
and the date of birth field is in the table called customer
select columns from customer where dateofbirth in
(select top 1 dateofbirth from customer group by dateofbirth order by count(*) desc)
Hi ,
thanks for the prompt reply , but i din understand the “top 1” word mentioned in here and it gave the following error when i tried to run the same
select cust_first_name, date_of_birth from customers where date_of_birth in(select top1 date_of_birth from
customers group by date_of_birth order by count(*)desc );
Error starting at line 1 in command:
select cust_first_name, date_of_birth from customers where date_of_birth in(select top1 date_of_birth from
customers group by date_of_birth order by count(*)desc )
Error at Command Line:2 Column:33
Error report:
SQL Error: ORA-00907: missing right parenthesis
00907. 00000 – “missing right parenthesis”
*Cause:
*Action:
Mote that this site is for SQL Server, For Oracle questions, try at Oracle forums
Hi,
This is very helpful and I will use one of the date methods, thank you. However if you are adding Order by Date which comes after the Select statement then the Convert wouldn’t work as a Date Field. Up to now I have been removing the minutes in the web front end by adding {0:d} which works well and allows the stored procedure to sort happily.
John
Uou need to use a different alias name for date column in the select statement and use original column name in the order by clause
hey,
what about just using
SELECT CAST(GETDATE() AS DATE)
Provided the server is 2008 or above version
This Site Rocks!
I want to display Date without Time on SSRS in a column, how I can do This
Manjeet
Reply me soon??????????
Use format option there
HOw I can Display Date without Time on SSRS
please tell me
i have problem to alter the database field from datetime to integer with values
alter table overtime alter column adate numeric(5)
while using this query am getting error like this
“Disallowed implicit conversion from data type datetime to data type numeric, table ‘DaiichiPayroll.dbo.Overtime’, column ‘adate’. Use the CONVERT function to run this query.
”
any solutions
You need to update that column to NULL before applying the alter statement