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.
SELECT GETDATE()

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

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

    Like

  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

    Like

  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

      Like

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

    Like

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

    Like

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

    Like

  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.

    Like

  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.

    Sachit

    Like

  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.

    Sachit

    Like

  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

    Like

  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

    Like

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

    Like

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

    SELECT * FROM MRO_MRSA where
    AdmissionDate BETWEEN DATEADD(D, 0, DATEDIFF(D, 0, @dateFrom)) AND DATEADD(D, 0, DATEDIFF(D, 0, @dateTo))

    Like

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

      SELECT * FROM MRO_MRSA
      where
      AdmissionDate >=DATEADD(D, DATEDIFF(D, 0, @dateFrom),0) AND
      AdmissionDate <DATEADD(D, DATEDIFF(D, 0, @dateTo)+1,1)

      Like

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

    Like

  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
    Sanu

    Like

    • 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

      Like

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

    mydate=”3/4/2008″
    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 !)

    Like

  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?

    Like

  18. Hi Vrushali,
    I hope you have already resolved your problem.
    Anyway this is very simple in fact.e.g
    “SELECT CONVERT(CHAR(11),GETDATE(),0)”
    will give you a date like ‘May 27 2008′

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

    Cheers

    Like

  19. Aslamo Alikom

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

    Like

  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.

    Like

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

      Like

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

    The Problem:
    1.
    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 ?

    Like

  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

    Like

  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
    getdate()
    else
    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.

    Like

  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?

    Regards
    Sachin

    Like

    • Yes. Refer this

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

      Like

  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?

    Regards,
    Lavee

    Like

  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.

    Regards,

    Shashi Kant.

    Like

  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.

    Like

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

    SELECT CONVERT(VARCHAR(10),GETDATE(),100)

    SELECT CONVERT(VARCHAR(10),GETDATE(),101)

    SELECT CONVERT(VARCHAR(10),GETDATE(),111)

    Like

  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
    ’09/13/2008′)

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

    but it’s giving count as 0.

    Please solve my problem.

    Like

  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

    Like

  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

    Like

  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

    Like

  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
    i.e.
    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

    Like

  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
    2008-10-26

    Like

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

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

    Like

  37. Answer for @Athar, @Preeti

    Query to retrieve only time from datetime column.

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

    OR

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

    Like

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

    Like

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

    Like

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

    Like

  41. @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,
    IM

    Like

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

  43. 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;
    and
    string strXML = ds.GetXml();
    and passing this strXML to SP.

    My XML have tag like :

    2009-02-28T00:00:00+05:30

    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(.)’,
    ‘DATETIME’)

    but it is giving NULL Value.

    Thanks.

    Reagerd

    Like

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

    2009-01-01T00:00:00+05:30
    2009-12-31T00:00:00+05:30

    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 )

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

    Regards

    Like

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

    Like

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

    Like

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

    Like

  48. @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.

    Like

  49. hello sir,

    column of a table contains data as

    jan09
    jul08
    aug08
    ………… 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

    Like

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

    Like

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

    Like

  52. @Alpesh,

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

    Please see a complete list of format styles here

    http://msdn.microsoft.com/en-us/library/ms187928.aspx

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

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

    Output :
    23:56:37

    GO

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

    Output :
    23:56:48:810

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

    Output:
    11:57:19:840PM

    ~ IM.

    Like

  53. @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???

    Like

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

    Sathish.

    Like

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

    Thanks,
    Sathish

    Like

  56. @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)

    ex:
    here
    uname as varchar(20)
    lastlogin as datetime
    created as datetime

    query:

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

    if yours is different case i’m sorry.

    Thanks,
    Sathish.

    Like

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

    CREATE TRIGGER [TRIGGER NAME] ON [dbo].[TABLE NAME]
    FOR INSERT, UPDATE, DELETE
    AS
    UPDATE [TABLE NAME] SET [COLUMN NAME] = CONVERT(datetime,CONVERT(varchar(20),[COLUMN NAME], 111), 111)

    It works very effectively.

    Like

  58. 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…

    Like

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

    Like

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

    Like

  61. 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)

    Like

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

      Like

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

    Tejas

    Like

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

    Like

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

    Like

  65. @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.

    Like

  66. @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.

    Like

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

    Like

  68. 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 ??

    Adi

    Like

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

    thanks.

    Regards
    Adi

    Like

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

    Like

  71. Hir Mrs Adi

    I’m very very very thank for you

    you give me the good solution good work for you.

    Bye

    if you want send to me your Email Address.

    Bye again

    Like

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

    Thanks,

    Ash

    Like

  73. 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))’.

    Like

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

      Regards
      PRAVEEN MUDGIL

      Like

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

    Like

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

    Regards,
    Pinal Dave

    Like

  76. 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!

    Like

  77. I NEED TO KNOW THAT,THERE IS A FIELD CALL DATE,AND ANOTHER HAVE VALUE
    DATE VALUE
    DATE1 100
    DATE2 200
    DATE3 300
    DATE1 200
    DATE3 100
    I WANT TO GET RESULT LIKE THIS
    DATE1 TOTAL VALUE= 300
    DATE 2 TOTAL VALUE=200
    ……….. .
    HOW CAN I DO THIS IN PHP CORD

    Like

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

    Like

    • 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'))
      begin
      insert into #tempdate1 select @date1
      set @date1 = dateadd(dd,1, @date1)
      end
      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.

      Like

  79. Hello Hurb,

    Union two statements, one for each column as below:

    select startdate from table
    union
    select enddate from table

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

    Regards,
    Pinal Dave

    Like

  80. 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
    @ydate:
    2010-03-15 10:07:59.107
    @ctdate:
    2010-03-16 10:07:59.107
    @dtdiff:
    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.

    Like

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

    select
    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…
    thanks

    Like

  82. 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 —

    Like

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

    Like

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

    Like

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

    Like

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

      Like

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

    Like

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

    Like

    • 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

      Like

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

    Like

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

    Like

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

        Like

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

    Like

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

    Like

  92. Hi pinal,

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

    CONVERT(VARCHAR(10),GETDATE(),111)-CONVERT(VARCHAR(10),date,111)

    Like

  93. 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();

    Like

  94. 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…

    Abhijit

    Like

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

    Like

  96. SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))
    return same result in case of varchar and datetime input
    but
    SELECT CONVERT(VARCHAR(10),GETDATE(),111)
    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

    Like

  97. 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′

    Like

  98. Hi,

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

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

    Like

  99. 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'

    Like

  100. 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'

    Like

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

    Like

  102. 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?

    Like

  103. 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.
    Sri

    Like

  104. I tried both solutions:
    SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))

    SELECT CONVERT(VARCHAR(10),GETDATE(),111)

    And I got better performance in option 1.
    SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))

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

    Like

  105. 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 :
    01/02/2009
    03/01/2009
    04/06/2009
    05/03/2009
    06/12/2008
    07/02/2009

    result should be :
    06/12/2008
    03/01/2009
    01/02/2009
    07/02/2009

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

    Like

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

    Like

  107. 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).

    Like

  108. 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’

    Like

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

    Like

  110. 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?

    Like

  111. 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….

    Thanks
    Sethu

    Like

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

    Like

  113. 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
    like
    EmpNo Date
    1 5-MAR
    1 6-MAR
    1 7-MAR
    1 8-MAR
    1 9-MAR
    1 10-MAR

    Like

  114. 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?

    Like

  115. 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…

    Like

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

    Like

  117. 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 ?

    Like

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

    Like

  119. 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 ?

    Like

  120. 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’
    with
    (
    FIELDTERMINATOR=’ ‘,
    ROWTERMINATOR=’0x0A’

    )

    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.

    Like

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

    Like

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

    Like

  123. 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….

    Like

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

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

    Like

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

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

    Like

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

    Like

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

    Like

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

  131. Hi All,

    I have created a stored procedure like below.

    CREATE PROCEDURE [dbo].[SP_CONSULTATION]
    @time time,
    @time1 time,
    @AGE numeric(3, 0)=5,
    @AMOUNT numeric(6, 2) OUTPUT
    AS
    BEGIN
    SET @time= LEFT(DATEADD(mi, 10, (CONVERT(TIME(0),GETDATE()))),5)
    –SET @time= CONVERT (time, SYSDATETIME())
    SET @time1 = (select @time)
    select @time1
    SET @AMOUNT= CASE
    WHEN @time1 ’18:00′ THEN
    CASE
    WHEN @AGE <= 10 THEN 200
    ELSE 500
    END
    ELSE
    CASE
    WHEN @AGE = 5 AND @AGE <= 10 THEN 100
    ELSE 200
    END
    END
    END

    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.

    Thanks,
    Avinash P

    Like

  132. if date is inserted in table like ‘Jan 5 1980 12:00AM’
    then how can i change the format like this 05/01/1980

    Like

  133. Jawalikar, as has been said, you should only use these functions for query related issues. Your application is more likely to be portable and break less if you use the end-user application for date formatting. I know this doesn’t answer your question, but as a developer I’ve had to clean up messes by past devs who used SQL for date formatting, it will work, but there are much better ways.

    Like

  134. Hi How are you? I need a help in converting BIGINT to DATETIME. Sample BIGINT Value 201411132245 i into 2014-11-13 22:45 (Date Hour Minute). Could you please help me . Thanks in advance…

    Like

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