SQL SERVER – Retrieve – Select Only Date Part From DateTime – Best Practice

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 date.

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

I approved his method though, I finally suggested my method using function CONVERT.

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 suggest many format are displayed on MSDN.

Some claims that using CONVERT is slower then using DATE functions but it is extremely negligible. I prefer to use CONVERT.

Here is the video discussing the same concepts.

Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL

About these ads

382 thoughts on “SQL SERVER – Retrieve – Select Only Date Part From DateTime – Best Practice

  1. HI.
    I want to display the date in U.S. format.
    Instead of Getdate() iam specifying directly the date.

    I have tried using these syntaxes.. It is not changing to that format.
    Kindly help

    Select convert(varchar,’10/09/2007′,1) ,
    Select convert(varchar,’10/09/2007′,101),
    Select convert(varchar,’10/09/2007′,110)

  2. The convert and string method has more overhead than you think if you need to use it in large rowsets.
    Also, you still need another CAST to turn it back into datetime.

    An alternate method one is to cast to float, FLOOR, and cast back to datetime.

    Finally: the function usually is non deterministic if you use string to datetime (or vice versa) because the date format is culture and locale sensitive. CAST and CONVERT also mess up determistism.

    Using the DATEADD(..DATEDIFF..) allows determinism, keeps all calculations within the datetime datatype, and guarantees complete locale insensitivity.

    Determinism is important for indexing computed columns, or if the function is used in WHERE clause

  3. Pingback: SQL SERVER - Introduction and Example for DATEFORMAT Command Journey to SQL Authority with Pinal Dave

    • you are too much…i never knew that something like this exist.
      i have been having problem exractin date from smalldatetime,and you have solve the problem for me

  4. Hello,
    I am extracting year from date in oracle i. e. to_char(sysdate,’yyyy’) Now i want to change my database from oracle to SQL Server 2005. So the point is how to convert
    to_char(sysdate,’yyyy’) from oracle to SQL Server 2005.
    Plz help me out.

    Thanking you,

  5. Thnax Buddy Nice Examples its working a lot….
    i personally feel u should add some more exapmles
    for sql queries which screw the programming day to day
    any ways thanx

  6. I want to convert varchar type date into datetime so i am getting the following error.

    Solve this Problem….

    “Msg 242, Level 16, State 3, Line 1
    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.”

    • It means that some of the values can’t be converted to datetime values. Can you post some of the sample data? Also what is the format that date values are stored?

  7. But what if I need the date to be 2007-12-14, with hyphens! SQL Server only offers me 2007-12-14 00:00:00, I don’t need the time at all.

  8. I m lloking for a sql server query which will display the report between two dates from the database where datetime field is defined.


  9. I m looking for a sql server query which will display the report between two dates from the database where datetime field is defined.


  10. Thanks

    It works best for me
    Actually from last 2 weeks i m searching this query

    and finally i m very happy that i will now process my project

  11. i’m working in Asp.net with backend of SQL Server.. i hav doubt for Compare Two Different Column in Table..Like ( Date_From and Date_To) i need to search for Date_From and Date_To when i entered Date_From TextBox and Date_To Textbox .. it ll compare Two Columns and retrive the Date to fill in Grid.. still i’m not able to Get the Concept .. how to Find this? if anybody pls rep as soon as possible..

    thanks in advance

  12. Sir,
    if db have more than one field having DateTime datatype than how can one specify the particular field in a sql query ?

  13. Helped a lot!!
    my query was as follows (viral this might help you)

    AdmissionDate BETWEEN DATEADD(D, 0, DATEDIFF(D, 0, @dateFrom)) AND DATEADD(D, 0, DATEDIFF(D, 0, @dateTo))

    • Same problem here dude can u provide me solution , if u get actual soln.

      Thanks & Warm Regard

      Amit Kumar
      [removed phone number and email address]

    • If input dates have time too, you may need to change the logic to

      AdmissionDate >=DATEADD(D, DATEDIFF(D, 0, @dateFrom),0) AND
      AdmissionDate <DATEADD(D, DATEDIFF(D, 0, @dateTo)+1,1)

  14. i want 2 disply date part from datatime datatype..
    so wrote a query
    “select convert(datetime, month_payroll, 102) as month_payroll from monthly_rates_swap”
    & i get o/p as = “2008-04-29 13:44:17.937″

    but i want o/p = ’29/04/2008′

    can any1 pla tell me tht how to convert datetime into simple date…coz in my company, datetime datatype is used for date…
    n i want 2 dipsly only Date in SQL..not date & time…
    i searched a lot on net..but i dnt get any ans…
    plz help me.
    thx in advance..

  15. Hi Dave

    Just very curious how to seperate Time from Datetime?what is the best way to store and retrieve time alone in a database like 03.00 am or 3.00 p.m ?

    Best regards

    • I suggest you to use datetime column
      When you input time values like ’3:00 AM’, it will get stored as

      1900-01-01 03:00:00.000′

      You can get time part easily and do all kinds of date related calculations

  16. My problem is not reading the date but inserting it into the database in ASP. eg

    Insertqry=”Insert into mytable ( [birthday]) ) values ( ‘ & CDATE(mydate) & “‘)

    results in 4/3/2008 going into the database.

    session.LCID=3081 often solves this kind of problem, but not here.

    (I can use parameters I believe, but I’m stubborn. There must be a way of doing it how I want !)

  17. If i convert date using above method, it will convert into varchar data type, but further we can’t operate date function on it… what to do?

  18. Hi Vrushali,
    I hope you have already resolved your problem.
    Anyway this is very simple in fact.e.g
    will give you a date like ‘May 27 2008′

    You can experiment with different arguments for interesting results which suits your requirement.


  19. Aslamo Alikom

    I’ve one qeustion that i wnat to use “like” word instead “=” in the query that retrieve the Date…. thanks

  20. I have date in varchar eg ’20080602-13:47:08.603′
    wnat to extract milliseconds only like 603,could you please tell me the way.

    • 1 Always use proper DATETIME datatype to store dates
      2 declare @d varchar(30)
      set @d=’20080602-13:47:08.603′
      select datepart(millisecond,cast(replace(@d,’-’,’ ‘) as datetime))

  21. Hmm I Have A Problem:
    I Have a Value Like this: Date: ’04/05/2005′

    The Problem:
    Select * FROM tblTest
    where Date LIKE ‘%2005%’

    Result: I Have 4 records

    2. Select * FROM tblTest
    where Date LIKE ‘%05_2005%’

    Result: I Have 2 Records (’04/05/2005′, ’12/05/2005′)

    3. Select * FROM tblTest
    where Date LIKE ‘%04_05_2005%’
    Or Date LIKE ’04/05/2005′
    Or Date LIKE ‘&04/05/2005&’

    Result : 0 recods :S:S
    WHere Is The Problem , Can u Help Me ?

  22. Hi Pinal,
    Your articles are very useful to all.

    I am working in database backup utility.
    I neet to select only from & to date data.
    But tables has no column of date datatype.
    Is SQL Server give the data to which date it is inserted into database.

    Thanks In Advance

  23. Hi all,
    How do I make this work? Thanks in advance

    select count(*) from
    where convert(modfd_date, varchar(10),110) =
    select convert(mon_dt, varchar(10), 110) from (select case
    When datepart(weekday, getdate()) = 2 Then
    dateadd( day, -datepart(weekday, getdate() -2), getdate() )
    End as mon_dt) t

    If I use convert function I get the error “‘varchar’ is not a recognized built-in function name.”

    What is the best way to select data on a date column without using the time part. (other than the option stated above ie., DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())) )

    I have an index on the date column and would like to use it. Not sure if SQL Server will use an index if a function is applied to a indexed column.

    Thank you for any help.

  24. Hi

    If we give Month and Year, Can we get the Start Date and End Date of the Month for that particular year. is it possible?


    • Yes. Refer this

      declare @month int, @year int
      select @month=5, @year=2008
      	dateadd(month,@month-1,dateadd(year,@year-1900,0)) as first_day,
      	dateadd(month,@month,dateadd(year,@year-1900,0))-1 as last_day
  25. Hi,

    I am using MSSQL server and I want to retrieve all records from [case] table where date_received is like ’12-02-2008′ and for this I am using below listed query (1) but its not returning me any record since there are matching records present in table.

    1. select (select convert(varchar, date_received, 110) as date from [case]) where date_received like ‘%12-02-2008%’

    2. select (select convert(varchar, date_received, 110) as date from [case]) where date_received like ‘%2008%’

    Query 1 does not return any record but 2 retuns me results.

    Can anyone please help me out?


  26. Respected Sir,

    I am developing a Payroll software in VB6.0 and using SQL as backend.

    I have a problem that i cant subtract the time from the datetime datatype

    Please help me in subtracting datetime.


    Shashi Kant.

  27. Hello Sir,

    I need a query to retrive record from the table based on day alone,month alone,year alone.for eg if i run that query in the august i will get the record which and all created in that month.

  28. hi i ha too mare solutions watch it in different formats




  29. HI,

    this is mahalakshmi. I am doing leave application using C#.net. In that iam checking existing dates are there or not. I have used below query

    SELECT COUNT(LeaveApplicationID) AS Expr1
    FROM LeaveApplication
    WHERE (CONVERT(CHAR(10), FromDate, 101) BETWEEN ’09/09/2008′ AND ’09/13/2008′) AND (CONVERT(CHAR(10), ToDate, 101) BETWEEN ’09/09/2008′ AND

    Fromdate is 09/12/2008 and todate is 09/18/2008.

    but it’s giving count as 0.

    Please solve my problem.

  30. hi all
    i have 6 columns with data in it but after giving this command
    (select * from income where dt between ‘sep 7 200′ and ‘sep 9 2008′)

    command is executed successfully but the output is
    am getting only the names of the columns without data in it
    please help me out

  31. hi
    i working on one project ie payrol system in that i hv to calculate employee present days automaticaly including late mark and all
    plz help me hw i can calculat time diff
    i insert time in database using date time picker

  32. how can i get date in the format “dd/MM/yyyy” with time included init.i have done a lot of googling.but cant able to manage.

    tried with

    SELECT CONVERT(VARCHAR(26), GETDATE(), 130)——> 28 Apr 2006 12:39:32:429AM

    SELECT CONVERT(VARCHAR(25), GETDATE(), 131)—–> 28/04/2006 12:39:32:429AM

    but they are returning a garbage values

  33. i had use the following function to get only the date part from datetime data type.

    set @data=CONVERT(varchar(8), @feedbackdate, 112)

    it did work fine and returned the date part as 20081005

    but while again using the procedure to insert the values
    Insert into tblfeedback (feedbackid,customerid,feedbackdate,feedbacktime,description) values(@maxUserId,@customerid,@data,@feedbacktime,@description)

    again it gets changed into the same and displays the time too
    please give me a solution for the same thankx

  34. Pingback: SQL SERVER - Retrieve - Select Only Date Part From DateTime - Best Practice - Part 2 Journey to SQL Authority with Pinal Dave

  35. DECLARE @ret_string varchar (255)
    DECLARE @year as varchar(10)
    DECLARE @Month as varchar(10)
    DECLARE @Day as varchar(10)

    set @year = cast(year(getdate()) as varchar)
    set @Month = cast(month(getdate()) as varchar)
    set @Day = cast(Day(getdate()) as varchar)

    EXEC xp_sprintf @ret_string OUTPUT, ‘%s-%s-%s’, @year, @Month, @Day

    PRINT @ret_string

    Result will be like this


    27 Oct 2008 00:17:58:423


    10/27/08 12:25:10 AM

  37. Testing for December 31st to get resultset of data for anything greater or equal to the 31st, but keep getting all of 2008 data.

    select * from TABLE where enddate >= CONVERT(VARCHAR(10),’2008-12-31′,111)

  38. Answer for @Athar, @Preeti

    Query to retrieve only time from datetime column.

    select convert(varchar(10), getdate(),108) –without millisecond


    select convert(varchar(10), getdate(),114) –with millisecond

  39. Hi,
    I want to retrieve only the date part from datetime from the SQL database. I am using
    SELECT CONVERT(VARCHAR(10),GETDATE(),111) as sowDate
    But it is giving me the current date :(
    Plz help me.

  40. I have a problem with dates that are entered in MS Access but are out-of-range like 01/02/830. Can u please help me with a query that can retrieve such dates for correction before I can import the data to SQL Server 2008.

  41. Hi,

    Thanks for your blog..

    I have a problem in comparing 2 date fields one has the time field as (2009-01-16 12:32:50.690) and the other (2008-08-07 00:00:00.000).

    I want to compare the date part of the 2 fields. I tried coverting them to varchar (101) and using the <= operator but the result set was incorrect.

    I am not sure how else I can achieve this functionality of comparing the date part only.

  42. @Ankit,

    You need to use styles with convert.
    Format of convert is

    convert ( New Data Type, Column_Name or variable, Style)

    select A.* , B.* from table_name A , table_name B
    where convert ( varchar(10), A.Date_column_name , 101) = convert ( varchar(10), B.Date_column_name , 101)

    Hope this helps,

  43. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  44. Hello Sir,

    I have one date problem regarding importing XML Date to sql Server 2005.

    I am passing DataTable to SP from .NET Application C#..
    the columns with DateTime data type having
    Default value as :
    col.DefaultValue = DateTime.Today.Date;
    string strXML = ds.GetXml();
    and passing this strXML to SP.

    My XML have tag like :


    SP is not accepting the date I pass above to datetime field of SQL server 2005.

    can you plz. suggest wt could i do for this..

    I tried for

    select cast(2009-02-28T00:00:00+05:30′ as xml).value(‘xs:dateTime(.)’,

    but it is giving NULL Value.



  45. Hello Sir..

    My above reading DataTime from XML get solve using above mentioned casting.

    Due to my some mistake in Query I was getting NULL value.

    Well, but now I am fasing new problem for this DateTime only of XML…

    I have two tags in my XML datatype string which are


    Actualy these dates are 01/01/2009 and 31/12/2009 respectively

    But In OpenXML when I convert these dates it gives me different OUTPUT

    (Here with I am showing you in select )

    cast(’2009-01-01T00:00:00+05:30′ as xml).value(‘xs:dateTime(.)’,’DATETIME’) as StartDate,

    cast(’2009-12-31T00:00:00+05:30′ as xml).value(‘xs:dateTime(.)’,’DATETIME’) as EndDate

    OUTPUT is :

    2008-12-31 18:30:00.000 2009-12-30 18:30:00.000

    respectively ..

    Can you please suggest Why this is..?


  46. Namasthe Pinal

    Could you tell me why my DB return is different?

    select getdate()

    3/3/2009 3:11:56 PM

    I wish it was

    3/3/2009 15:11:36

  47. Date representation in the DB and Date formatting are two different things.

    You take too simplistic approach.

    For example, in a native date field, I can perform date arithmetic, but not in a varchar, or string field.

    Hence please dont treat them as one and the same.

    Oracle has a native date field – 01-01-2009 01:12:35
    You can apply a “trunc” function to chop off all time, i.e.
    you will get a native ’01-01-2009′.

    On both the above values, you can do arithmetic, i.e. add hours, days, or any time interval – you will get a new date as a result.

    Both the above dates, trunc’ed one, and the full date,
    can be formatted into a varchar or string.

    Hence date presentation is different from date.

    This is such a common trap programmers fall into.

  48. Dear All,

    I been trying to find an optimize way to fetch the data on datetime columns, and preferably i would go for dateadd method i.e. DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())), the reason why i would choose this method over convert is because of my size of table which is almost like 2 millions rows and somehow my datetime column is a part of primary key. Also to note is, if my concern is only to get the date part then its better to use above mentioned method than using convert, as convert will change the datatype of column on the fly to string and its an overhead for the engine to do this step. Surely Pinal might have a better solution but i been using the dateadd for quite a while now and its going smooth.
    Happy coding.

    Regards- Rahman

  49. @Rahman

    If the date part gets queried a lot, it may be worthwhile to have a computed COLUMN in the TABLE that applies the FUNCTION for you. Then, INDEX that COLUMN.

  50. hello sir,

    column of a table contains data as

    ………… like this

    how can i select only last two digits of that column

    select right(column_name,2) from table_name;
    is not retrieving any data

    pls help me

  51. hello,
    i hav used datetimepicker control in my form.it stores date and time both in sql server database.
    is there any way to only store time, not date in sql server database?also same for retrieval??
    Thank You.

  52. hello,
    i hav used datetimepicker control in my form.it stores date and time both in sql server database.
    is there any way to only store time, not date in sql server database?also same for retrieval??
    Thank You in advance

  53. @Alpesh,

    You can use convert styles, to get different formats for date.

    Please see a complete list of format styles here

    I have used only 3 formats (108, 109, 114) just to show example:

    – only Time ( 24 hours clock)
    select convert ( varchar , getdate() , 108)

    Output :


    – only Time with Micro seconds ( 24 hours clock)
    select convert ( varchar, getdate(), 114)

    Output :

    – only Time with Micro seconds ( 12 hours clock)
    select substring ( convert ( varchar, getdate(), 109), 13, len ( convert ( varchar, getdate(), 109)) – 12)


    ~ IM.

  54. @Imran

    sorry tosay but I asked how to store only time in sql server database . I know how to extract only time/date from tht control…..Plz…How To STORE???

  55. Hi All,

    I have a table with two datetime fields, now I want to display both columns as date only.

    tablename: sometable with two fields as

    created | lastlogin
    2009-06-02 22:02:05.033 | 2009-06-02 22:03:34.503
    2009-06-02 22:02:27.530 | 2009-06-02 22:03:50.047

    now I want output as

    created | lastlogin
    2009-06-02 | 2009-06-02
    2009-06-02 | 2009-06-02

    can anyone explain please, Thanks in advance


  56. I have been through all the replies and got it..

    SELECT CONVERT(VARCHAR(11),created,102) as created_date, CONVERT(VARCHAR(11),lastlogin,102) as last_date from sometable


  57. @Alpesh

    declare the the field whatever you want to insert only time as varchar then
    while inserting use this function on that field
    convert ( varchar(20) , getdate() , 108)

    uname as varchar(20)
    lastlogin as datetime
    created as datetime


    insert into table_name (uname, lastlogin, created) values (convert ( varchar(20) , getdate() , 108), getdate(), getdate())

    if yours is different case i’m sorry.


  58. I developed the solution for date only from ‘getdate()’ function. Also this worked for me very effectivelyy. Because I does not required to change the data type in the database or not required to write down different function for each time or not required to execute the same function for so many times.
    I use this method.
    1) Keep data type of column as ‘datetime’.
    2)Keep its default value to ‘getdate()’.
    3) Write trigger on that table as

    UPDATE [TABLE NAME] SET [COLUMN NAME] = CONVERT(datetime,CONVERT(varchar(20),[COLUMN NAME], 111), 111)

    It works very effectively.

  59. FWIW…

    Many people prefer the DATEADD method rather than the CONVERT method because they are trying to compare two dates rather than output a date…

    If you use the CONVER method, once you get the string with just the date in it, you have to convert it back to a datetime to do any comparisons with it other than =, and two converts is more expensive than a couple of system function calls in a non-negligible way…

  60. I have a problem of trying to find dates in my table which does not follow the following formats. Given are the valid formats:

    1. 10/1/2007
    2. Thu Sep 20 06:32:10 GMT-0400 (EDT) 2007
    3. 10/1/2007 5:00

    Can anybody please help me regarding this?
    Thanks in advance.

  61. please help me i want to get data between two dates im writing

    select * from Test_Date where t_Date>=convert(datetime,’02/02/2009′,103) and t_Date<=convert(datetime,'02/02/2010',103)
    can not get the correct data

  62. please help me i want to get data between two dates im writing ,these query act normaly but not correct data

    select * from Test_Date where t_Date>=convert(datetime,’02/02/2009′,103) and t_Date<=convert(datetime,'02/02/2010',103)
    can not get the correct data

    and the datatype of column t_Date is varchar(50)

    • Three things

      1 Always use proper DATETIME datatype to store dates
      2 Work on DATETIME and not VARCHARs
      3 You should always use unambiguious format YYYYMMDD HH:MM:SS

      So your query should be

      select * from Test_Date where convert(datetime,t_Date,103)>=’20090202′ and convert(datetime,t_Date,103)<'20090203'

  63. Hi Rabin,

    select *
    from Test_Date
    where CAST(t_Date AS DATETIME)>=convert(datetime,’02/02/2009′,103) and CAST(t_Date AS DATETIME)<=convert(datetime,'02/02/2010',103)

    let me know if it helps you.


  64. I have a datetime column in database in ’9/29/2009 1:00:10 AM’ this format. Based on this coulmn value i need to get other column value but datetime value would be in ’9/28/2009′ format.
    How I can achive following query
    select * from tblFirst where Startdate=’9/29/2009′
    but in Database Startdate column has value as ’9/29/2009 1:00:10 AM’

    Could you help me?
    Thanks in advance

  65. Hi Mrs Tejas Shah thank for you

    ok your code is correct but actuall not get

    the specific result because if we put from date

    ’01/03/2009′ to date ’01/01/2010′ we cannot get the

    record has date ’02/03/2009′ why ?

    Notes: i’m save date in database as dd/mm/yyyy

    date column in database has datatype varchar can you help in this code or another way to get the good result.

    Thank again for you

  66. @Rabih,

    Check if this could help.

    Create Table #Example ( Eid int, EmpName varchar(50), DateJoined varchar(50))

    Insert into #Example values ( 1, ‘Bill Gates’ , ’01/01/2009′)
    Insert into #Example values ( 2, ‘Gustavo’ , ’01/02/2009′)
    Insert into #Example values ( 3, ‘Catherine’ , ’01/03/2009′)
    Insert into #Example values ( 4, ‘Kim’ , ’01/04/2009′)
    Insert into #Example values ( 5, ‘Humberto’ , ’01/06/2009′)
    Insert into #Example values ( 6, ‘Pilar’ , ’01/08/2009′)
    Insert into #Example values ( 7, ‘Frances’ , ’01/10/2009′)
    Insert into #Example values ( 8, ‘Margaret’ , ’01/12/2009′)
    Insert into #Example values ( 9, ‘Carla’ , ’01/01/2010′)
    Insert into #Example values ( 10, ‘Jay’ , ’01/02/2010′)
    Insert into #Example values ( 11, ‘Ronald’ , ’01/03/2010′)

    select * from #Example

    select *
    from #Example
    where Convert (Datetime, DateJoined) between ’01/03/2009′ and ’01/01/2010′

    Drop table #Example

    ~ IM.

  67. @W.Ah

    Check if this could help.

    Create Table #Example ( Eid Int, Ename varchar(40), JoinDate datetime )
    insert into #Example Values ( 1, ‘Imran’ , ’9/29/2009 1:00:10 AM’)

    Select * from #Example

    Select Eid, Ename from #Example
    Where convert (datetime, convert ( varchar, JoinDate, 101 )) = ’9/29/2009′

    Drop table #Example

    ~ IM.

  68. Hi Mr Imran Mohammed

    thank for this code but not actuall get the result please i need all records between two dates your code not gell all records.
    In my table
    date column in my table is varchar and i’m put date in my application like dd/mm/yyyy and i’m writing this code

    select * from Test_Date where t_Date>=convert(datetime,’01/03/2009′,103) and t_Date<=convert(datetime,'02/02/2010',103)

    not get the record has date '02/03/2009′ try this code and see the result please help

    thanks again

  69. Hello Rabih,

    r u getting follwoing error :

    “The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
    while using ”

    select * from Test_Date where t_Date>=convert(datetime,’01/03/2009′,103) and t_Date<=convert(datetime,'02/02/2010',103)

    statement ??


  70. select * from #TempTable Where convert(numeric,Convert(varchar,cast((left(right(t_Date,7),2) +’/’ + left(t_Date,2) + ‘/’ + right(t_Date,4)) as datetime),112)) >=convert(numeric,Convert(varchar,convert(datetime,’01/03/2009′,103),112)) and
    convert(numeric,Convert(varchar,cast((left(right(t_Date,7),2) +’/’ + left(t_Date,2) + ‘/’ + right(t_Date,4)) as datetime),112)) = and <= condition

    Hope it will give u all the results u r expecting.

    Do reply.



  71. Rabih
    it seems my select statement is not comming compleately
    I did following steps.

    Flow is like this :
    1) arrange ur string dd/MM/yyyy to MM/dd/yyyy
    2) then convert it to Datatime
    3) then convert that datetime to numeric format using 112
    4) Now convert ur From date and ToDate of conditon to datetime 103
    5) then convert those From date and toDate to numeric format
    6) and then compare using >= and <= condition

  72. Hir Mrs Adi

    I’m very very very thank for you

    you give me the good solution good work for you.


    if you want send to me your Email Address.

    Bye again

  73. Can Datepart be used as part of update…set query. I am trying to update a datefield by the following statement

    Update tablexx

    Set Datepart(yyyy, Datefld) = 2011.

    But I am getting errors.



  74. Hi All,

    Kindly look at this statement:

    strSQL = “SELECT * FROM LogTbl WHERE logDateTime=(SELECT MAX(logDateTime) FROM LogTbl);”

    The problem here is that the actual value which is stored in the table looks like this: 12/2/2009 4:06:55 AM

    Now the fetched record will be the latest one. But I want to fetch all the records of that date that is: mm/dd/yyyy.

    So, how can we do that in the above mentioned Query Statement? Can anybody help me with it, please? Otherwise, I will have to store the time in a seperate filed in the table which is not that much efficient and worthwhile.

    Using your example I tried this but it doesn’t work:

    strSQL = “SELECT * FROM LogTbl WHERE logDateTime=(SELECT logDateTime FROM LogTbl CONVERT(VARCHAR(10),logDateTime,111));”

    This is the error:

    Technical Information (for support personnel)

    Error Type:
    Microsoft JET Database Engine (0x80040E14)
    Syntax error. in query expression ‘logDateTime=(SELECT logDateTime FROM shiftLogTbl CONVERT(VARCHAR(10),logDateTime,111))’.

    • hi Dev

      Use this, it will solve your prblm. I think..

      SELECT * FROM LogTbl WHERE CONVERT(VARCHAR(10), logDateTime, 110)=(SELECT MAX(CONVERT(VARCHAR(10), logDateTime, 110)) FROM LogTbl)


  75. Thanks Mr.Pinal. This is Praveen from Xtend Technologies, cochin. I was looking for a solution to get the distinct date part of a date time field in SQL server.

  76. Hello Praveen,

    If you are using SQL Server 2008 then you can get distinct date by using below statement:

    SELECT DISTINCT CONVERT(DATE,(datetime_column)) FROM tableName

    Otherwise use below:

    SELECT DISTINCT CONVERT(VARCHAR(10),(datetime_column)) FROM tableName

    Pinal Dave

  77. I have a date column, like ‘yyyy-mm-dd’, so i want to retrieve the data from table with only a perticular year..
    Please help me..


  78. i need help in visual basic and sql. when i click date in vb components of calendar1 in selected date tat should be access and show through the msflexgrid or another form.
    Please help me soooooon!

    DATE1 100
    DATE2 200
    DATE3 300
    DATE1 200
    DATE3 100
    ……….. .

  80. Hello,
    I am doing a project in access 2007.

    I have a table with two fields: startdate and enddate.

    i wanted to have an sql statement that will return all the dates within startdate and enddate.

    Is this possible?

    Thank you very much.

    • hi hurb,
      maybe the below soln will help you,

      create table #tempdate1(date datetime)
      declare @date1 datetime
      set @date1 = ’2010-02-10′
      while (@date1 <= '2010-02-15'))
      insert into #tempdate1 select @date1
      set @date1 = dateadd(dd,1, @date1)
      select * from #tempdate1
      drop table #tempdate1

      you will get the dates from 2010-02-10 to 2010-02-15 (based on the condition in the while, where changing the condition in the while you can get different results based on the condition).
      you can have this one in a SP and have two input parameters such as startdate and enddate and pass the values to these variables and you can achieve your required soln by minimal changes in the above code.

  81. Hello Hurb,

    Union two statements, one for each column as below:

    select startdate from table
    select enddate from table

    Let us know if this doesn’t fulfill your requirement.

    Pinal Dave

  82. Hello sir,

    I want to set only the date in 2 variables and compare them.

    I used the following code

    declare @ydate as datetime,@ctdate as datetime,@dtdiff as datetime
    set @ydate=current_timestamp-1
    set @ctdate=current_timestamp
    select @ydate
    select @ctdate
    set @dtdiff=datediff(dd,@ydate,@ctdate)
    select @dtdiff

    and got the output as
    2010-03-15 10:07:59.107
    2010-03-16 10:07:59.107
    1900-01-02 00:00:00.000

    I don’t want this output . I need compare the values and find whether the @ctdate is greater than @ydate.

    Please help me out.

  83. If I need to get a rolling weekly average (week starting on Monday) how would i write that?

    COUNT(*) as numOfFiles,
    NumberOfLoans as numRecords,
    convert(char(10),dateadd(week,-1,dateadd(day,-1*(datepart(weekday,[CreateDtTm])-1),[CreateDtTm])),101) as WeekStartDate,
    convert(char(10),dateadd(day,-1*(datepart(weekday,[CreateDtTm])-1),[CreateDtTm]),101) as WeekEndDate
    from testtable

    I can only get my start and end of the week, how would I go about calculating the daily/weekly/monthly averages? Any help in the right direction is greatly apprecaited…

  84. I need a lill help from you all smartys!

    I am working on an update of a datetime field and receiving the following error.


    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)


    Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near ‘)’.

    Please assist.

    Thanks, — imransi —

  85. 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

  86. 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..

  87. 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 :)


    • 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
      insert into @temp select @day, count(DAY(incidentdate)) from phl_csapp
      where DAY(incidentdate) = @day
      set @day = @day + 1
      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.

  88. 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.

  89. 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”)

    Dim pToDate As SqlParameter = New SqlParameter(“@ToDate”, SqlDbType.Date, 10)
    pToDate.Value = Request(“tbToDate”)



    Dim rwReader As SqlDataReader = cmd.ExecuteReader
    dgA.DataSource = rwReader


    End If

    End Sub
    – =============================================
    – Author: Caz
    – Create date: 22/09/2010
    – Description: Orders between dates
    – =============================================
    – Add the parameters for the stored procedure here
    @FromDate date,
    @ToDate date
    – SET NOCOUNT ON added to prevent extra result sets from
    – interfering with SELECT statements.

    — Insert statements for procedure here

    from Orders
    OrderDate >= @FromDate
    and OrderDate <= @ToDate
    order by OrderDate


    Am I missing something here or just being stupid?
    Any help gratefully received.

    • 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))
      Dim dGrid As New DataGrid()
      dGrid.DataSource = dbcmd.ExecuteReader()
      Catch ex As Exception
      End Try

  90. 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”)
    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
    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.
    – =============================================
    – 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
    – SET NOCOUNT ON added to prevent extra result sets from
    – interfering with SELECT statements.

    — Insert statements for procedure here

    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
    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

  91. 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

      • 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”

  92. 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.

  93. 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

  94. Hi pinal,

    Convert function doesn’t work when we apply airthmatic operators with it for e.g


  95. Hi:
    Can anyone please help me how to convert string ‘yyyy/mm’ as datetime yyyy/mm/dd in sql 2005 ?

    Thanks in advance

  96. can anybody tell me how to display only date part from database…i’ve taken datetime filed in database…

    my corrent coding is

    snet.XCmd x = new snet.XCmd(“select * from Personal where email=@p1″);
    x.Cmd.Parameters.AddWithValue(“@p1″, Session["email"].ToString());

    DataTable dt = x.GetTable();

    txtFname.Text = dt.Rows[0][2].ToString();
    txtMname.Text = dt.Rows[0][3].ToString();
    txtLname.Text = dt.Rows[0][4].ToString();

    txtGender.SelectedValue = dt.Rows[0][5].ToString();
    txtDob.Text = dt.Rows[0][6].ToString(); //i wanna show here only date
    bool bt;
    bool.TryParse(dt.Rows[0][7].ToString(), out bt);
    txtIsFullDate.Checked = bt;
    txtAdrs.Text = dt.Rows[0][8].ToString();
    txtCity.Text = dt.Rows[0][9].ToString();
    txtState.Text = dt.Rows[0][10].ToString();
    txtPin.Text = dt.Rows[0][11].ToString();
    string country = dt.Rows[0][12].ToString();

  97. Hi Pinal,

    it’s a really helpful write up on getdate()…oit helps me alot…i am workin on a script that gives previous date in output only…

    select dateadd(dd, datediff(dd, 0, getdate())-1, 0);

    this query gives me the output in 2011-03-08 00:00:00.000 this format…

    can u help me to get the output in “08-03-2011″ this format…


  98. Sir… You are really a genious in SQL.Your SQL queries provide immense help to all. But i do have a question?

    Can you give me the SQL query to retrieve data based on current date and time where my database table has two seperate fields for Date and Time whose data types are date and time(0) respectively. I am stuck in this query. I need to retrieve data from database.
    And at the same time i need another query to add time of 3 hours to the current time and retrieve those datas from database.
    Any help will greatly be appreciated

    return same result in case of varchar and datetime input
    output varies in case of varchar and datetime input

    below is the example
    select convert(varchar(10),convert(datetime,’5/13/2011′,111),111)

    Select convert(varchar(10),’2011-05-13 14:46:34.000′,111)
    both statement return different result

  100. Hi,

    I want to retrieve the records based on the column ‘INSERT_DATE’ which is having the datetime format as ’2010-12-09 21:03:13.966′

  101. Hi,

    How to faster way to retrieve the data through select statement?

    And before going to partition what are the things need to remember?

  102. Hello Friends,
    Can any one help me quickly where am doing mistake in the below queries… If I run the first below query its giving me output in 4 to 5 seconds of time…

    SET @sqlQuery = ‘INSERT INTO #temp SELECT CAST(Datepart(year,shpm.shipdate)as varchar) +
    RIGHT(”0” + CAST(Datepart(week,shpm.shipdate)as varchar),2) as workweek,
    Substring(snm.sn, 1, 4) AS fs4_sn,snm.sn as sn,
    CONVERT(VARCHAR(10),shpm.shipdate,121) as ship_date FROM sntrax_current..sn_master snm
    LEFT OUTER JOIN sntrax_current..sn_ship_link shplnk
    ON snm.sn_identity = shplnk.sn_identity
    LEFT OUTER JOIN sntrax_current..ship_master shpm
    ON shplnk.ship_identity = shpm.ship_identity
    WHERE shpm.shipdate >=”’+ @startdate + ”’AND
    shpm.shipdate =”+ @startdate + ”AND
    shpm.shipdate <='' + @enddate + '' AND
    snm.sn like ''+ @sn + '%' order by shpm.shipdate desc'

  103. But if I take out the query from parameter @sqlQuery and run it then its taking lot of time(approximately 4 to 5minutes)

    Can anyone help me what is the right Query to get it without @sqlquery parameters

    INSERT INTO #temp SELECT CAST(Datepart(year,shpm.shipdate)as varchar) +
    RIGHT(’0′ + CAST(Datepart(week,shpm.shipdate)as varchar),2) as workweek,
    Substring(snm.sn, 1, 4) AS fs4_sn,snm.sn as sn,
    CONVERT(VARCHAR(10),shpm.shipdate,121) as ship_date FROM sntrax_current..sn_master snm
    LEFT OUTER JOIN sntrax_current..sn_ship_link shplnk
    ON snm.sn_identity = shplnk.sn_identity
    LEFT OUTER JOIN sntrax_current..ship_master shpm
    ON shplnk.ship_identity = shpm.ship_identity
    WHERE shpm.shipdate >=”+ @startdate + ”AND
    shpm.shipdate <='' + @enddate + '' AND
    snm.sn like ''+ @sn + '%' order by shpm.shipdate desc'

  104. Your VARCHAR(10) should be at least VARCHAR(12).

    SELECT CONVERT(VARCHAR(10),’Jun 6 2011 9:35AM’,111), CONVERT(VARCHAR(12),’Jun 6 2011 9:35AM’,111)

    should prove my point.

  105. What if you have an actual field in a table that contains both date and time of an entry and you only want the date part? E.G. the field is timelogged and it contains the date/time in the format YYYY-MM-DD 00:00:00.000

    How would I go about getting only the YYY-MM-DD part?

  106. Hi,

    I’d like to convert a datetime field to varchar in YYYY MMM DD format.

    I’m expecting the same result as the following SQL:
    select SUBSTRING(CONVERT(varchar(20),GetDate(),113),8,4) + ‘ ‘ + SUBSTRING(CONVERT(varchar(20),GetDate(),113),4,3) + ‘ ‘ + SUBSTRING(CONVERT(varchar(20),GetDate(),113),1,2)
    — output: 2011 Aug 18

    Is there any easy way (something like SELECT CONVERT(VARCHAR(12), GETDATE(), 106)) of doing this without substring?

    Thanks in advance.

  107. I tried both solutions:


    And I got better performance in option 1.

    I get 3 second executing the query with option 1 and with option 2 was around 5 second.

  108. i have problem with accending date…. my current syntax is
    SELECT * FROM mytablename ORDER BY datefield ASC. right now my datefield using VARCHAR…
    my result are :

    result should be :

    can anyone teach me the simple way to solve this problem and give some help please…. ^_^

  109. Ok, I guess not so short:
    The convert function is rounding the value. So dates that has passed noon will become tomorrows dates — oops.

    thus correct value would be:
    Convert(Int, convert(float, GetUtcDate()))

    Converting from float to int will truncate the value.

  110. What about using the numerical rappresentation of a DateTime?
    CONVERT(INT,GetDate()) give the date part of datetime rappresentation.
    So my suggestion is to use CONVERT(DATETIME, CONVERT(INT,GetDate())).
    Another advantage to integer cast versus string cast is that it’s simpler to use in ordering, filtering and so on (think about BETWEEN).

  111. I have a vachar field in DB to store dates (which is actually creating problem).Now i can’t change the field type as i contains real data and lots of other application uses this DB. i want to design a query which compares dates i.e select * form Emp where joining_date(its in varchar type) between ‘datefrom’ and ‘dateto’

  112. how i select date value from sql?. I used ms acces i used ‘ datevalue’ key word. but in sql ‘to_date’ key word is not working. any one please help me?. How i retrieve a date value in sql through java?. Please help me.

  113. Hi i have a issue can you please help.

    I have 35 K records (email address) i need to pull out only the domain part (after @) and update to another column.

    How to do this?

  114. Sir,
    I want select data from table orber by register date in desc order and mssql register date field type is nvarchar.
    what is query i need to use…

    Please help me urgently….


  115. Thanks for the site, I get many benefits.

    I create a view that pulls down safety data from corporate site based on local id. I then need to derive the number of day’s since last incident based on certain codes and do other calculations. This will end up on ASP web page. Should I create another view or table or maybe even do sql code in ASP? Here is my select:

    SELECT *
    FROM [SAFETY_WEB].[dbo].[vw_HSEL]
    WHERE (inrCode = ‘HS3′) OR (inrCode = ‘HS4′) OR (inrCode = ‘HS5′) OR (inrCode = ‘HS6′) OR (inrCode = ‘HS7′) OR (inrCode = ‘HS9′)
    ORDER BY EventDate DESC, inrCode

    Not sure how to get number of day’s since last incident and put it in a field
    SELECT CONVERT(VARCHAR(10),GETDATE(),111) – EventDate of Top 1

    Because of the ORDER BY above, the Top EventDate is the latest incident.

  116. There is a table “Leave”
    in which three columns are there namely

    1st – EmpNo
    2nd — From Date
    3rd — End Date

    Employee No. 1 has taken leave from 5th march to 10 march

    so record will be

    1 5-MAR-2012 10-Mar-2012

    i want result in vertical
    EmpNo Date
    1 5-MAR
    1 6-MAR
    1 7-MAR
    1 8-MAR
    1 9-MAR
    1 10-MAR

  117. Can I comapre date fileld (with Getdate)without using Convert or cast method which will work faster than Convert/Cast ?

  118. select convert(varchar(4),datediff(dd,getdate(),’01/01/2009′)) giving result as * instead of truncating the value
    please can you clear me on this?

  119. why not u all simply use !!!!!!!!!!!!!!!! what the rocket science in this ..?????
    select CONVERT(date, getdate())

  120. Thx.. I need to update the part of the date my date is 02/05/1987 i am having 40000 records.. kindly suggest some solution… my all dates are random and i want make them look like as 01/month/year… means just need to set the day as 01 (update table set day(date.)=01 gives syntax. error…

  121. thnx madhivanan but in my case this will nt wrk.. i am having some dates like
    2012-01-10 00:00:00.000
    2013-01-18 00:00:00.000
    2014-01-22 00:00:00.000
    2015-01-17 00:00:00.000
    2016-01-18 00:00:00.000
    2017-01-29 00:00:00.000
    2018-01-01 00:00:00.000
    2018-05-15 00:00:00.000
    2020-01-20 00:00:00.000
    2021-01-01 00:00:00.000
    2022-05-12 00:00:00.000
    2023-03-25 00:00:00.000
    2024-06-06 00:00:00.000
    if i run this query on these will nt b changed.. as i need to convert these dates as year+01+01 for alll

  122. I have date 2012-07-09 08:07:02.360 in database i want only ’2012-07-09′ this part will return in select statement. Can you help me ?

  123. This result fulfill my requirement for difference between start time and end time
    SELECT DATEDIFF(minute,(SELECT (CONVERT(VARCHAR(10),GETDATE(),111)+’ 08:00:00.000′)),(SELECT (CONVERT(VARCHAR(10),GETDATE(),111)+’ 15:30:00.000′)))

    Diff between current date time stamp and today other time:
    SELECT DATEDIFF(minute,current_timestamp,(SELECT (CONVERT(VARCHAR(10),GETDATE(),111)+’ 15:30:00.000′))) AS DiffDate

  124. When i am not passing date value . The database is taking default value 1900-01-01 00:00:00.000 .How to restrict this. i wnt to pass NULL value when i am not assigning any date.

    Any Suggestions ?

  125. Hello Sir
    here my problem is i have to get values from a txt file using bulk insert

    BULK INSERT tbl_Trial
    FROM ‘C:\Output\inout.txt’


    this is the sp am using for that.

    1 2012-07-05 17:09:41 1 0 1 0
    1 2012-07-05 17:09:49 1 0 1 0
    1 2012-07-05 17:09:51 1 0 1 0
    2 2012-07-05 17:11:23 1 0 1 0
    2 2012-07-05 17:11:35 1 0 1 0
    3 2012-07-05 17:15:41 1 0 1 0
    3 2012-07-05 17:15:43 1 0 1 0
    1 2012-07-05 17:19:51 1 0 1 0
    1 2012-07-05 17:19:55 1 0 1 0

    this is the txt format

    Question is
    How to insert the txt file values to sql server table with time and date separately using stored procedure.

  126. Hi sir,
    I have used this query ORDER BY cast([Regdate] as datetime) DESC but error occur was The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range
    please help me.

  127. Hi sir,
    when i use this query for sorting date wise order by CONVERT(datetime,[date],103) DESC
    Error occur was:
    The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

  128. Hi sir,
    when i use this query for sorting date wise order by CONVERT(datetime,[date],103) DESC
    Error occur was:
    The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
    Please help me urgently
    Thanks in adv….

  129. if the date is stored as varchar datatype and i need to extract only “year” from that date,could anyone help me out in this issue?

  130. Pingback: SQL SERVER – Display Datetime in Specific Format – SQL in Sixty Seconds #033 – Video « SQL Server Journey with SQL Authority

  131. Sir, I am raghu i am having problem with this query

    Q. Display the orderID of the top five orders based on the Total amount due in the year 2001.

    hint:You can extract the Year part from a date using the Datepart Function

    i am not getting the proper output

    i am using SSMS 2005, Database Adworks please reply ASAP.

  132. Pingback: SQL SERVER – Weekly Series – Memory Lane – #033 | Journey to SQL Authority with Pinal Dave

  133. this is helpful however, I bumped into problems when I used convert instead of dateadd function to extract the datepart. when you use this column to search for a specific date, the converted datetime column will yield incorrect results since it still has the timepart in it only not visible. in cases like this, it is advisable to use the dateadd function instead as you will be sure that it will set the time part to 00:00:00.000 hence you will be able to use this to look for a specific date only.

  134. I still don’t understand.. I have 3 dates in my table configured as
    10/10/2013 1:11:00 PM

    I need to extract each piece separately but this doesn’t work

    select datepart (mm, session_start) startMM, datepart(dd, session_start) startDD, datepart(yy,session_start) startYY,
    datepart (HH, session_start) startTimeHour, datepart(MI, session_start) startTimeMin,
    datepart(SS, session_start) startTimeSec, datepart (AM, session_start)

    What am I doing wrong? Please help.

  135. sorry if its not relevant
    I want to generate date from 2010-01-01 to 2013-12-31 then find GenerateDate(Which will be 2010-01-01 for first data), Year (Which will be 2010 for 2010-01-01). Month(Which will be 01 for 2010-01-01), Day(Which will be 01 for 2010-01-01), Week(Which will be 1 for 2010-01-01), Quarter (Which will be 01 for 2010-01-01) then I want to create a Date_Table and insert all data in the SQL table. There will be a auto increment filed (ID) also. Here GenerateDate will be Primary Key Please help me.

  136. Pingback: SQL SERVER – Weekly Series – Memory Lane – #051 | Journey to SQL Authority with Pinal Dave

  137. Hi All,

    I have created a stored procedure like below.

    @time time,
    @time1 time,
    @AGE numeric(3, 0)=5,
    @AMOUNT numeric(6, 2) OUTPUT
    SET @time= LEFT(DATEADD(mi, 10, (CONVERT(TIME(0),GETDATE()))),5)
    –SET @time= CONVERT (time, SYSDATETIME())
    SET @time1 = (select @time)
    select @time1
    WHEN @time1 ’18:00′ THEN
    WHEN @AGE <= 10 THEN 200
    ELSE 500
    WHEN @AGE = 5 AND @AGE <= 10 THEN 100
    ELSE 200

    when i execute this stored procedure i got error like this

    Msg 201, Level 16, State 4, Procedure SP_CONSULTATION, Line 0
    Procedure or function 'SP_CONSULTATION' expects parameter '@time', which was not supplied.

    (1 row(s) affected)

    (1 row(s) affected)

    i want the output as to meet the conditions in stored procedure and want to display the amount value. please help me out from this.

    Avinash P

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s