SQL SERVER – Retrieve Current Date Time in SQL Server CURRENT_TIMESTAMP, GETDATE(), {fn NOW()}

There are three ways to retrieve the current datetime in SQL SERVER.
CURRENT_TIMESTAMP, GETDATE(), {fn NOW()}

CURRENT_TIMESTAMP
CURRENT_TIMESTAMP is a nondeterministic function. Views and expressions that reference this column cannot be indexed. CURRENT_TIMESTAMP can be used to print the current date and time every time that the report is produced.

GETDATE()
GETDATE is a nondeterministic function. Views and expressions that reference this column cannot be indexed. GETDATE can be used to print the current date and time every time that the report is produced.

{fn Now()}
The {fn Now()} is an ODBC canonical function which can be used in T-SQL since the OLE DB provider for SQL Server supports them. {fn Now()} can be used to print the current date and time every time that the report is produced.

If you run following script in Query Analyzer. I will give you same results. If you see execution plan there is no performance difference. It is same for all the three select statement.
SELECT CURRENT_TIMESTAMP
GO
SELECT {fn NOW()}
GO
SELECT GETDATE()
GO

Performance:
There is absolutely no difference in using any of them. As they are absolutely same.

My Preference:
I like GETDATE(). Why? Why bother when they are same!!!

Quick Video on the same subject

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

About these ads

457 thoughts on “SQL SERVER – Retrieve Current Date Time in SQL Server CURRENT_TIMESTAMP, GETDATE(), {fn NOW()}

  1. Actually i worked in Oracle 10g. Now i got prj in Sql Server 2005. Here i feel little difficult in formatting Date.
    Can u tell me how to format Date as ” DD\Mon\YYYY”.
    Here in Sql Server 2005 gives both DateandTime.

    • can you help me with retrieving the the most current date and counting it? For example if 10 request came in today i want to display 10.

      I am using MSSQL version unknown. I am also programming in PHP, thanks. Any help will b greatly appreciated.

    • can you help me if there is any Facility or command in SQL Server 2005 ..
      to Execute the stored procedure on particular time span..

      I want to execute stored procedure /Function @ 00:01AM (Automatically) on daily basis..

      i m using ASP.Net

      I m fresher …Plz kindly tell me if possible..

      • Hi Vinod,

        You can create an individual job for this under “SQL Server Agent” option.
        Create one new job there and then you can mention your query like
        Exec [Dbo].[SPName].
        After this you can schedule its date and time as per your requirement. And then your job will be execute accordingly.

        Thanks,
        Yash Thakkar

    • To refer back to the article:

      SELECT {fn CURRENT_DATE()};

      My preference is also GETDATE () but it is T-SQL specific. CURRENT_DATE feels closer to Oracle syntax:

      SELECT CURRENT_DATE FROM DUAL;

  2. Hi,

    Don’t feel that i’m appreciating Oracle. i’m New for Sql Server.

    Just now i worked u r example.

    Actually i need a Date in this format ( 25-Jun-2007).

    In Oracle we use as follows,

    Query:
    select to_char(’25/06/2007′,’dd-Mon-YYYY’) from dual;

    Result:
    25-Jun-2007

    In Sql-Server 2005, i tried following query.
    Query:

    select substring(convert(varchar(10),getdate(),111),len(convert(varchar(10),getdate(),111))-1,2) + ‘-‘
    + substring(convert(varchar(20),getdate(),0),1,3)+’-‘+substring(convert(varchar(10),getdate(),111),1,4)as Dat

    Result:
    25-Jun-2007

    See the Length of the query, can u give me a short query to format Date in Sql-Server 2005

    Regards
    S. Ramkumar

  3. Hi Ramkumar,

    I prefer SQL Server over Oracle cause several reasons. One of them is simplicity.

    First you should go over to post my previous comment.
    Second see the reference of Book On Line.
    which is BOL Convert. There are many examples of different date formats. Just use the one you need.

    Regards,
    Pinal Dave(SQLAuthority.com)

  4. Hi,

    Thankyou Mr. Pinaldave. i saw that on-line book. There are lot of styles were given for Date conversion. I think style no: 105 and 106 will satisfy my requirement.

    Thankyou again. Bye.

    Regards
    S. Ramkumar

  5. Hi, am learning SQL SERVER 2005. How i do condition in Date like

    SELECT…
    FROM…
    WHERE EndDate < …………….

    I have try currentdate, SYSDATE but it error. Please provide a code of Current date thank.

    Didier Peinke

  6. Hello

    I have a table in sql server 2005 which contain a column for date.
    I want to put a validation that if user does not specify the date, it takes current date and time.
    Is it possible by setting the ‘Default value’ for that column?? if yes how can i dothat.
    Please help me.
    Thanl you

    Jigna

  7. When I use ISQL to execute “select getdate()” or “select current_timestamp” or “select {fn NOW()}” the timestamp returned is “yyyy-mm-dd hh:mm:ss.ttt”. When I run the same “select” in ADO the timestamp is returned as “dow-mmm-dd hh:mm:ss timezone yyyy”….what gives? I need the timestamp exactly as it appears in ISQL…

    Thanks

    Steve

    • Select * from customert where replace(convert(varchar(20),date,106),’ ‘,’-‘) between replace(convert(varchar(20),’17-May-10′ ,106),’ ‘,’-‘) and replace(convert(varchar(20),’19-May-10′ ,106),’ ‘,’-‘)
      it will work!!!!

  8. this query does not retrieve data?
    select *
    from Main_Table
    where Todays_date <= convert(varchar,datepart(yyyy,getdate())) + ‘-‘ + convert(varchar,datepart(mm,getdate())) + ‘-‘ + convert(varchar,datepart(dd,getdate()))

  9. I am trying to do something very simple, but can’t seem to get it to work. I am using a PHP include to update certain parts of my website. What I want to do is take the date in the start_date column add n number of days to it, then run the PHP script. Can anybody give me any help on this?

    Thanks so much!!!!

    Tran

  10. Hi,
    i was wondering if i can ask, if anyone knows how to reference the getdate() function in a table, in SQL express, from a vb.net form. My current query is

    SELECT *
    FROM table1
    WHERE date = ‘textbox1.text ‘

    this however does not work, but if i put the date in its binary date format in the textbox this works, i can select something, does anyone have any ideas about a conversion??…..

    Thank you in advance.

    ashlesh

  11. hi, i need more queries about date and time.
    i have a problem.
    i) how to store only time with AM or PM notation in datetime type
    ii) how display time with out milliseconds

    i need immediate solution.

    regards,

    thanks.

  12. Hi Lakshmi,

    You may use the following :

    SELECT LTRIM(RIGHT(CONVERT(CHAR(26),GETDATE(),109),14))

    There might be a better way of doing it. Maybe Guru (Mr. Dave) can help us.

    All the best.

  13. Hi again Lakshmi,

    You may also use the following :

    SELECT RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100),7)

    Regards,
    Vishwanath

  14. Hi once again Lakshmi,

    You may create a function to get the time part and can use the function wherever you want. Create a function as follows :

    CREATE FUNCTION [dbo].[udf_GetTime] ( @myDateTime DATETIME)
    RETURNS VARCHAR(50)
    AS
    BEGIN
    DECLARE @RetDate VARCHAR(50)
    SELECT @RetDate = RIGHT(CONVERT(VARCHAR(50), @myDateTime, 100),7)
    RETURN @RetDate
    END
    GO

    — Use the function like this :

    SELECT [dbo].[udf_GetTime](GETDATE())

  15. Hi Pinal,

    create table employee
    (
    emp_id varchar(5) NOT NULL,
    fname varchar(20)NOT NULL,
    lname varchar(30)NOT NULL,
    job_id smallint,
    hire_date DATETIME DEFAULT GETDATE());

    insert into employee values(‘00003′,’Joe’,’Wys’,’0001′,”);

    in above table, i need to get current datetime, but unfornately I am not getting it….follwoing is the result :–
    00003 Joe Wys 1 1900-01-01 00:00:00.000

    What may be the reason ?

  16. Nitin –

    You are overwriting your default value when you specify ” as your last value. You can not supply a value if you want the default to kick in. When you supply ”, the system puts the blank datetime in, which is 1900-01-01 00:00:00.000. The correct insert would be

    INSERT INTO employee (emp_id, fname, lname, job_id) VALUES (‘00003, ‘Joe’, ‘Wys’, ‘0001’);

    This should keep the default value for the hire_date.

      • Create this function and find the days in month and calculete LOP=DaysinMonth-LWP

        Create FUNCTION [dbo].[fun_GetDaysInMonth] ( @pDate DATETIME )
        RETURNS INT
        AS
        BEGIN

        RETURN CASE WHEN MONTH(@pDate) IN (1, 3, 5, 7, 8, 10, 12) THEN 31
        WHEN MONTH(@pDate) IN (4, 6, 9, 11) THEN 30
        ELSE CASE WHEN (YEAR(@pDate) % 4 = 0 AND
        YEAR(@pDate) % 100 != 0) OR
        (YEAR(@pDate) % 400 = 0)
        THEN 29
        ELSE 28
        END
        END

        END

    • Create this function and find the days in month and calculete LOP=DaysinMonth-LWP

      Create FUNCTION [dbo].[fun_GetDaysInMonth] ( @pDate DATETIME )
      RETURNS INT
      AS
      BEGIN

      RETURN CASE WHEN MONTH(@pDate) IN (1, 3, 5, 7, 8, 10, 12) THEN 31
      WHEN MONTH(@pDate) IN (4, 6, 9, 11) THEN 30
      ELSE CASE WHEN (YEAR(@pDate) % 4 = 0 AND
      YEAR(@pDate) % 100 != 0) OR
      (YEAR(@pDate) % 400 = 0)
      THEN 29
      ELSE 28
      END
      END

      END

  17. hello sir, i am facing a problem in sql , i want to know the command which is used to know the no. of days where two dates were given.
    for ex.
    28/10/2008
    10/11/2008
    then what is the no. of days between these dates.

    thanking you..

  18. hi

    can you tell me how to retrive data from any table by using current date we will not give date in where condition but the table have date colum
    eg

    select * from abc where date=todaysdate.

    system should take the todays date.

  19. how to add validity numbers of years in start date (datetimepiker1) and after add years output last date in (datetimepiker2)

    Please very important in my projects

    reply me

  20. I upsized an Access application (with data) to SQL 2005.

    Application works fine with the ‘date’ data that was uploaded, but not with new dates that are added.

    The dates LOOK like they are in the same format, but there must be a difference.

    If I run
    select * from census
    where censusdate= ‘4/10/2008′
    I get all the records returned.

    If I run
    select * form census
    where censusdate= ‘5/1/2008′
    nothing gets returned, even though in the table there are 89 records with the date 5/1/2008.

    These records were added with the following
    UPDATE vth_census SET vth_census.censusdate = getDate()
    WHERE (((vth_census.censusdate) Is Null));

    (I’ve tried different date formats……05/01/2008, etc. All the possibilties work with the 4/10/2008 date, not with the 5/1/2008 date.)

    I’ve narrowed down the problem to the date issue, but I can’t figure out the solution.

    Any suggestions?
    Thanks,
    Jill

  21. hi,

    i have one query,
    i want to find the difference between the time saved in data base and current time
    and the condition is
    if the difference is > 30 the respected rows only should display.

  22. Hi,

    I want the day of a given date. Like Monday, Tuesday. There is a similar question, but no answer. Pls help

    Nanthu

  23. Hi There, when asking for the date/time difference between 2 dates, is it possible to get it formated in hh:mm:ss but the hours incur +24 per day.

    ex.

    2008/5/6 04:56:55 PM – 2008/5/5 03:22:25 PM

    I need it to return 25:34:30
    instead it returns 1901/01/01 01:34:30

  24. Hi, i need de getdate from my local computer..when i use the getdate() bring me the date from the server…

    Tks…

  25. Hello,

    I need to format the date in that way:

    20080506

    it is yyyymmdd and with the sentence that I have tested:

    DECLARE @day as DATETIME
    SET @day = convert(varchar,datepart(yyyy,getdate())) + ‘’ + convert(varchar,datepart(mm,getdate())) + ‘’ + convert(varchar,datepart(dd,getdate()))

    I get this: 200856, without zeros.
    Even defining each varchar length (varchar(2)) I get the same.

    What should I do?

    Thanks!!!!

  26. I have found the solution to my question. I didn’t know that there was a list of formating stiles.

    To get 20080507 the code number is 112.

    SET @data = convert(varchar,getdate(),112)

    Thank you anyway

  27. here you go Teo

    SELECT CAST(DATEPART(YEAR,GETDATE()) AS VARCHAR(4)) + RIGHT(‘0′ + CAST(DATEPART(MONTH,GETDATE()) AS VARCHAR(2)),2) + ‘0’ + RIGHT(‘0′ + CAST(DATEPART(DAY,GETDATE()) AS VARCHAR(2)),2)

  28. Hi,

    I need to find the current year for the previous year

    For ex,
    Select yr_mnth,…
    from …
    where yr_mnth in (200603,200602,200601)

    consider the year 2006 is the previous year…and I want the results for 2007(i.e,for current year)

    result

    200703 …
    200702 …
    200701 …

  29. Hi..
    I am new to this one… plz body can help me..
    i have one query in sql like.. using the current year , i have to get the data previous year as well as current year…

    what should i do?

    Thanks!

  30. hi im vicknesh here , im havinga difficulty when exract the date according date , where i need to extract the member details which includes member expiry dates on 31stmay 2008 .

    can i put as follows … WHERE (vip.vip_expirydate => {d ‘2008-05-31′})

    • What happened when you tried?
      If dates included time too, use

      WHERE
      vip.vip_expirydate >= '‘20080531' and
      vip.vip_expirydate < '‘20080601'

      Note that YYYYMMDD format is unambigious

  31. how I can select all record in last week
    table action
    {
    Actiondate datrtime
    ………
    primary key(Actiondate)
    }

  32. Hi Everyone
    can any one find solution for this qury

    I have one column which contains Date field. and I want date difference between two rows like (1st row- 2nd row),(2st row- 3nd row) 3-4,4-5 like that.

    Thanks in Advance
    VishwanathReddy

  33. Hi
    can we get only time in datetime(),if anyone knows plz update………

    ex:

    I need it to return 01:34:30
    instead it returns 1901/01/01 01:34:30

    Thanks
    Kathir

  34. Hi S. Ramkumar,
    u can use convert function and u will get the value as u want

    convert(datetime,getdate(),101)

  35. can u tell me, how to dispay time like this “1994-11-05T08:15:30-05:00″ corresponds to November 5, 1994, 8:15:30 am, US Eastern Standard Time.using sql server 2005

  36. Hi,
    can any one help me Please. i have a date column “DateTime” populated with 2008-05-01 11:45:58.380 with thousands of dates , when i run report /Excute i would like to get result ” DataTime” as 2008-05-01 only, not 2008-05-01 11:45:58.380. Is there any way to truncate the date field??

  37. Hello,

    I have entered this trying to get the current date into my table with the field named InvoiceSentDate. Is this the correct usage b/c the column keeps coming back null.

    select @InvoiceSentDate = GETDATE()

    Thanks,
    Nathan

  38. This worked for me: –

    SELECT dbo.AuditTrail.ATTime,
    CONVERT(char(12), dbo.AuditTrail.ATTime, 3) ,
    CONVERT(char(12), dbo.AuditTrail.ATTime, 8) ,
    CONVERT(char(12), dbo.AuditTrail.ATTime, 10) FROM dbo.AuditTrail WHERE CONVERT(char(12), dbo.AuditTrail.ATTime, 3) = ’01/05/08′

  39. Hello,

    I would like to format the CURRENT_TIMESTAMP. Suppose CURRENT_TIMESTAMP return ‘2008-02-09 11:46:17.827′. But I want ’09-02-2008′. Is it possible.

    With Regards.
    Biplab

  40. Dear Friends,

    I want to find only the differnece in the time in minutes and hours can u suggest me how can I do it

  41. Hello, i have a problem witn a stored procedure which uses datetime variables. The procedure looks like :

    BEGIN TRANSACTION

    DELETE FROM dbo.station
    WHERE stationId = @stationId
    AND auditDate = @time_stamp

    auditDate and @time_stamp are both DATETIME

    When I try to execute the procedure, it gives the following error : “Error converting data type nvarchar to datetime”

    If i try to use another date format , like mdy not dmy , there is no row updated. Any idees ?

    Thanks

  42. @tylo

    Its not the problem of datetime format, the problem is with Nvarchar and datetime. Either auditdate or stationid must be nvarchar datatype and you are trying to compare a datetime with a nvarchar datatype… which is not possible.

    Try using a convert function and then compare, it would be helpful if you post your complete query….

    Thanks,
    Imran.

  43. @ sathish,

    select datediff ( dd, ‘01.23.1985’, ‘02.23.1985’)

    Result: 31

    Hope this helps.

    Thanks,
    Imran.

  44. hi all
    Can anybody help me out to calculate the age of a person…in
    years-month-days like 23 years-9 months-10 days
    we are having a column as DOB(date of birth) in one table….
    I want to calculate using current date….

    thanx in advance

  45. @jitender Pal,

    I would use some thing like this for your question,

    declare @date varchar(100)
    set @date = ‘2.23.2005’
    select (convert (varchar(10), datediff (yy, convert (datetime , @date), getdate()))+ ‘ Years- ‘ + convert (varchar(10), datepart (mm, (getdate())- convert (datetime , @date) ))+ ‘ Months- ‘ + convert (varchar(10),datepart (dd, (getdate())- convert (datetime , @date) ))+’ Days’) AS DOB

    You might want to change this code accordinly to your needs.

    Hope this helps.
    Thanks,

  46. Pingback: SQL SERVER - 2008 - Find Current System Date Time and Time Offset Journey to SQL Authority with Pinal Dave

  47. hi,

    I am new to sqlserver 2005, so pls help me

    i have @date=10:10:10 AM (hh:mm:ss am/pm format) as varchar datatype.

    I want to get the current sqlserver time as say @current_time and find the difference between @date and @current_time.

    Thanks,
    Prem.

  48. Hi
    Can u please help
    Iam having DateTime in database I need to compare only Time in the database with the current system time i.e comparing two times………………………

  49. I was going thru my sch project and this is a stupid course of mine~. Lectures don’t cover all these in detail

    Thanks for the GetDate function with a () , I guess it helped me to search for more resource and info on the net too !!!

  50. How can i display the detail which is after the day 4th march 1963
    i tried this one but shows no rows selected.
    where as there are rows after this date

    select * from agents
    where to_char(activation_date,’FMMonth DD, YY’) > ‘march 4, 1920′
    ;
    please help

  51. Jen,

    U can add one day by code below.

    select dateadd(dd, 1, getdate()) – add one day

    select dateadd(mm, 1, getdate()) – add one month

    select dateadd(yy, 1, getdate()) – add one year

    Same way you can subtract day, month or year. Just give negative value.

    select dateadd(dd, -1, getdate()) – subtract one day

  52. Hi Farhana,

    you can maintain column in database which indicate status of email address generated or not.
    For 24hr solution maintain one column which insert current date when data inserted in table.
    you can query database and get last day records. After that say u have unique user name in your system or firstname or something that u maintain unique for all users. Take that field value and concatenate it with domain you want to auto generate like “hello@example.com” and send this details to user or update the data whatever task you want.

    Hope this help you out.

    Thanks

  53. Hai,

    for example the manager when check the report , that time one hour before records only to display and it store automatcally in path.

    What is the query for that one?plz tell me step by step

  54. SELECT @DateTime AS Expr1, LastModifiedDate
    FROM dbo.tblProjectHistory
    WHERE (LastModifiedDate = @DateTime) AND (RecordId = 1)

    LastModifiedDate fields type date time
    LastModifiedDate=23/10/2008 2:18:51 PM
    @DateTime=23/10/2008 2:18:51 PM
    when run Query result null

  55. Need help with getting the correct time due to daylight saving time. I need to get current time from table but factor in as follows:
    If date is >= first Sunday in November and <= 2nd Sunday in March, then I need to subtract 1 hour from the date time I display on a report.
    Ideas?

  56. Hai Pinal dave,

    I have an column with datetime, I want to get the seconds from that column.

    For that what i could do

    Thanks in advance

  57. hi ,

    how to get the highest dates in the database
    for example in my query i want to get the highest and compare to the input date

    SELECT COUNT(*) AS Expr1
    FROM Booking_Item
    WHERE (‘2009-03-06′ ‘2009-03-06′)

    This is my query

  58. ================================
    Biplab

    Hello,

    I would like to format the CURRENT_TIMESTAMP. Suppose CURRENT_TIMESTAMP return ‘2008-02-09 11:46:17.827′. But I want ‘09-02-2008′. Is it possible.

    With Regards.
    Biplab
    ================================
    You can do like this:

    LEFT(CONVERT(varchar, CURRENT_TIMESTAMP, 103), 10)

  59. Hello i have 4 fields in my table
    1.Market Name,
    2.Commodity Name
    3.Price
    4.SubmissionDate Date_Format=(11/02/2009 01:58:27 )
    i wanna fetch a collection of records in manner of such format:
    one market ,all commodity and one recent Price submitted by user .

    i try my my side but i can’t do such thing.
    please help me.

    Thanks and Regards

  60. Do you know is there a way in SSMS to set up precision for showing how long does the query took? The one I see at the bottom in yellow?

  61. for Lakshmi’s Q. here is a better formated “TimeOnly” function (tweaked Vishwanath’s)

    CREATE FUNCTION [dbo].[udf_GetTime] ( @myDateTime DATETIME)
    RETURNS VARCHAR(50)
    AS
    BEGIN
    DECLARE @RetDate VARCHAR(50)
    SELECT @RetDate = RIGHT(CONVERT(VARCHAR(50), @myDateTime, 100),7)
    SELECT @RetDate = REPLACE(@RetDate, ‘ ‘, ‘0’)
    SELECT @RetDate = REPLACE(@RetDate, ‘AM’, ‘ AM’)
    SELECT @RetDate = REPLACE(@RetDate, ‘PM’, ‘ PM’)
    RETURN @RetDate
    END

    Displays your time in HH:SS AM/PM format.

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

  63. Is there a way to get the day as (sunday or monday ….) in sql server as we use to_char function in Oracle to get the current day.

  64. Pingback: SQL SERVER - Top Five Articles of Year 2008 Journey to SQL Authority with Pinal Dave

  65. dear sir,
    i am working in asp.net, i want to retrive Current Invitiondate of customere in result table.
    so i use its query

    select * from result where invitiondate = getdate()

    But that query is not sucessfully result
    plz send me this query

    thanks

    tejpal singh bajiya

  66. Sir,

    My current query:
    select *
    from workorder
    order by timecreated
    where timecreated >= DATEADD(day, -1, CURRENT_TIMESTAMP);

    I want to retrieve all date with today’s date. I am not a SQL PRO, used to be but have not done SQL for over ten years.
    “TIMECREATED” is the column where the work order’s creation date is stored in the format 1/14/2009 12:12:19PM

    Can you please help me sort this out. THANKS!

  67. Hi Peter,

    you can use this:

    select *
    from workorder
    where DATEADD(day, 0, DATEADD(dd,0, DATEDIFF(dd,0,timecreated))) >= DATEADD(day, 0, DATEADD(dd,0, DATEDIFF(dd,0,GETDATE())))
    order by timecreated

    Thanks,

    Tejas

  68. @Peter

    select *
    from workorder
    order by timecreated
    where timecreated >= CONVERT(DATETIME, CONVERT(VARCHAR(8), CURRENT_TIMESTAMP, 1), 1);

    That’s if none are in the future. If there are future entries, BETWEEN is required:

    select *
    from workorder
    order by timecreated
    where timecreated BETWEEN DATETIME, CONVERT(VARCHAR(8), CURRENT_TIMESTAMP, 1), 1) AND DATEADD(d, 1, CONVERT(DATETIME, CONVERT(VARCHAR(8), CURRENT_TIMESTAMP, 1), 1))

    @tejasnshah

    Pinal’s best practices rule #5 is “Avoid functions on columns.” http://blog.sqlauthority.com/2008/04/25/sql-server-optimization-rules-of-thumb-best-practices/

    our solution puts a FUNCTION on the COLUMN itself.

  69. sir,
    im currently doing a project on rfid which tracks attendance. and i am having difficulty in creating an sql query statement to be used in crystal reports.

    i am using 2 database tables
    1)holds the employee attendance details retrieved from the rfid
    2) general employee details, more like a database of employee details

    attendance details:table contents
    employee id
    card id
    employee name
    department
    systemdate
    time in
    time out
    lost
    mark delete

    the time in and out values are provided by teh rfid kit
    while the other details are retrieved from the employee details table.

    im supposed to be generating weekly, monthly reports based on the above values.
    and im finding it very difficult to learn querying withing such a short span of time .

    my weekly reports should contain the columns

    employeeid employee name no of days late no of days early out

    the start day and end dates are provided by the user at run time using a datepicker in vs2005.
    the report is supposed to show a consolidated value for no of days late and no of days early out columns during the time span provided by the user at runtime.
    any person entering teh gate after 8:45 is marked late and any person leaving before 4:10 is marked early out
    hence for each day that the person has left early or come late i have to show days in whole numbers as 1,2 ,3 etc for the above columns(early out and late in).. can u help me generate a query for teh above

  70. DEAR SIR,

    MY QUESTION IS HOW CAN I TAKE THE SERVER DATETIME, NOT THE STATTION DATETIME.

    I CREATE A PROCEDURE WITH A SELECT current_timestamp
    to pass the timer but return the station time not a server time.

    how i do this ?

    thanks and best regards

  71. I am using (right(CONVERT([char](8),getdate(),(108)),(10))) to get only the time part,however it is not showing the AM,PM part.How can I get the AM,PM part.
    Please suggest.

    Thank You

  72. hi,

    I want to fetch todays date from the whole database , not from any particular Table and that is already in database. please help me…!!!

  73. hi,
    I want to find the difference between the current date and the date stored in the database(I used datetime datatype to store the dates in the database).
    I m new to the sql server pleeeeeeeeeeeeese help!!!!

  74. Hi!

    I need a bit of unique help.
    Is there any way to retrieve the last updated records (for the last week say) if you DO NOT have the date stored anywhere in your table?

    I know this is a dumb question but then again it’s kind of a silly mistake to make when creating the table! N what the hell… it’s worth a shot rite?

    Could someone please tell me if they think this is at all remotely possible?

  75. am pretty new to sql queries…

    in the below query
    ‘———————————–
    SELECT USERS.USERS_NAME, COMPANY.COMPANY_NAME, LOGS.LOGS_DATETIME, LOGS.LOGS_ACTION
    FROM LOGS INNER JOIN
    COMPANY ON LOGS.LOGS_COMPANY_ID = COMPANY.COMPANY_ID INNER JOIN
    USERS ON LOGS.LOGS_USERS_ID = USERS.USERS_ID
    WHERE (LOGS.LOGS_COMPANY_ID = 11) AND (LOGS.LOGS_ACTION LIKE ‘uploaded%’) AND (LOGS.LOGS_DATETIME = GETDATE()) OR
    (LOGS.LOGS_ACTION LIKE ‘downloaded%’)
    ORDER BY LOGS.LOGS_DATETIME DESC
    ‘———————————–
    —i need to filter records from the log table which as uploaded and downloaded in logs_action field.

    this query should be run on the current date and teh cutoff time is 5.30pm for e.g. the query should run for today date and any file uploaded or downloaded before 5.30pm today should be shown.
    thanks a ton

  76. Hello,

    I am trying to get the number of days between a date span.
    If I have a span of three months I would like it to appear like the example down below. I would have the parameter as 01/01/2009 – 03/31/2009, but want the results split out. Can anyone help?

    Jan xxhours
    Feb xxhours
    March xxhours

  77. @Maegen

    Look at DATEDIFF() to find the intervals. If you have difficulty, post what you have tried and perhaps we can help.

  78. Hi,
    I need to get details from attandence table
    date,timein,timeout by chid id.

    In this table stroring one day n times timein and time out. But i need day first time in and day last time out
    plz send the result for this if any one know this is my mail id

  79. Hello to everyone

    SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS SystemDate, LEFT(RIGHT(CONVERT(varchar, GETDATE(), 0), 8), 6) + ‘ ‘ + RIGHT(CONVERT(varchar, GETDATE(), 0), 2) AS SystemTime

    Just copy the code above to obtain the Date and Time separate in each column the time format is HH:MM AM/PM that will vary in your system time and the date format is MM/DD/YYYY.

    Sample Output:

    SystemDate SystemTime
    07/23/2009 3:59 PM

    I hope you find it useful.

    God Bless to all :)

  80. Hello to everyone

    SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [SystemDate], LEFT(RIGHT(CONVERT(varchar, GETDATE(), 0), 8), 6) + ‘ ‘ + RIGHT(CONVERT(varchar, GETDATE(), 0), 2) AS [SystemTime]

    Just copy the code above to SQL Query or View to obtain the Date and Time separate in each column the time format is HH:MM AM/PM that will vary in your system time and the date format is MM/DD/YYYY. There’s so many person want to know how will you format time in HH:MM AM/PM now here’s the answer. luckily i find it out. :)

    Sample Output:

    SystemDate SystemTime
    07/23/2009 3:59 PM

    I hope you find it useful.

    God Bless to all :)

  81. Dear all
    i would like query from my table but we have many data record the same infor but i need query only on colum
    example depent only one type
    in my table have

    Row —mobile name—-type—–creae date—–install date
    1 mobile1 A 03/05/2009 03/04/2009
    1 mobile1 A 07/06/2009 07/06/2009
    1 mobile1 A 05/07/2009 05/07/2009
    1 mobile1 A 12/08/2009 12/08/2009

    for user normall i use
    mobile1 A 12/08/2009 12/08/2009

    Pleae help me because we have more information

  82. Hi,

    I want to create database backup file with current date and time.
    For example : database name is : RemoteDatabase
    Current Date Time is : 10 August 2009 11:29 AM

    How can I give this name to my .bak file?

    Or if it is not possible can I provide time only like
    RemoteDatabase_11:29AM.bak

    Thanks
    Vineeta

  83. Is it possible to put GetDate() or CURRENT_TIMESTAMP as a field’s default value? If yes, when I update a row, will this field update itself with the the timestamp of that update moment?

  84. @Lucas

    1. First question, Can Getdate() be used as default value to a column ?
    Ans: Yes, GetDate() can be used as a default value for a column.

    2. Second Question, when I update a row, will this field update itself ?
    Ans: No. It will not update by itself, you will have to update this manually.

    Consider below script as example.

    create table #Example1 ( Eid Int, Ename varchar(40), Date_Time datetime default (Getdate()))

    Insert into #Example1 (Eid, Ename) values ( 1, ‘SQL’ )
    Insert into #Example1 (Eid, Ename) values ( 2, ‘SQQ’ )

    Select * from #Example1

    — Now update statment.

    Update #Example1
    Set Ename = ‘SQL’
    , Date_Time = Getdate() — updating manually.
    Where Eid = 2

    Select * From #Example1.

    Drop table #Example1.

    How ever, this could be done automatically as well,

    You can have a FOR UPDATE or AFTER UPDATE Trigger created on the table.

    This trigger will fire when there is any update on the table, and inside this trigger, you can update your datetime column with the current datetime.

    You can also use, if update statement inside trigger, to be specific, mentioning if there is any update for these specific columns only then update date_time column.

    I dont suggest using trigger though, if you see many updates on the table. This could decrease your application performance significantly.

    ~ IM.

  85. Hi All,

    Can anyone tell me how to find the difference of (year/month/day) birth date to till date.

    i see the imran code but it is not perfect. I have to count actual year,month and date.

    thanks,
    Mazhar

  86. @Kanika,

    you can do that by using Datetime conversion functions available in SQL..
    like
    select convert(varchar(10),getdate(),105)

  87. Can we select any of them as field’s Default Value if field type is DateTime?

    Or

    Is there any solution for that?

    I need a column if it doesn’t specified on insert it’s default value should be current date time!

    • Formation matters only if you want to show dates in front end application. If you use front end application, use format function there. Otherwise read abot CONVERT function in SQL Server help file

  88. Thanks for your post.

    BTW the only one of the three that works within a DTS package against a legacy KB-SQL ODBC datasource is {fn NOW()}.

    I’m a newbie, and am guessing it is because the others aren’t understood by the KB-SQL’s flavour of SQL?

    Anyway, very glad that I remembered your post (which I’d already referred to for straight MSSQL queries) and found that one out of three worked in the context of what I was doing.

    So, while they’re all the same performance-wise, there is a crucial difference in breadth of application.

    Thanks again.

  89. hi
    i have problem in inserting the record depending on the current date. if current date change then record has to be inserted automatically.plz tel me how to write trigger or jobs in sql server2000

  90. Actually i’m finding that

    CURRENT_TIMESTAMP is correct, it returns the full date and time :
    2009-10-26 04:58:24

    GEDATE returns always return a time of 0 ( the date is correct though )

    example : 2009-10-26 00:00:00

    Si in my case i prefered to use CURRENT_TIMESTAMP because i sort my news by time, and i might have several news an hour.

    Pat.

  91. Hi,

    I need help on writing a query for both these scenarios :

    1. The current date is equal to or greater than 92 days past a semester start date.
    2. The current date is not greater than 92 days past a semester start date.

    Semester start date is a column..
    Thank you..

  92. Hi,
    I am new in sql server,
    i have some problem ,
    like when i write this code

    select * from [some table] where
    convert (datetime,’1/5/2009′,103′) >= getDate()

    when return the record is lose some of them .

    i hope help me .

    thanks

    • The correct way od foing it is

      select * from [some table]
      where
      date_col>= dateadd(day,datediff(day,0,getDate()),0) and
      date_col< dateadd(day,datediff(day,0,getDate())+1,0)

  93. Hi all,

    I have table in which hiredate is column and i want to calculate, Using HireDate of an employee the time they have worked. Basically i want to calculate the difference between hire date and current date of my system.

    Thanks a lot in advance.

    Jay

  94. for my report I specify the following:
    DECLARE
    @StartDate datetime,
    @EndDate datetime
    SET @StartDate = ‘2009-12-10 21:00′
    SET @EndDate = ‘2009-12-11 07:00′

    SELECT ……
    WHERE Datecolumn >= @StartDate
    and Datecolumn < @EndDate.

    For the report, instead of specifying date and time ('2009-12-10 21:00'), I want the users to only specify the time (21:00). Which is the best way to do this ?

  95. Hello Clive,

    If you are using SQL Server 2008 then you can convert the date variables to TIME data type.

    In earlier version use the following syntax:

    WHERE Datecolumn >= CONVERT (VARCHAR(5), @StartDate,108)
    and Datecolumn < CONVERT (VARCHAR(5), @EndDate,108)

    Regards,
    Pinal Dave

  96. é o seguinte precisava de uma ajuda vossa , quero visuallizar registos somente da semana passada , ou entao de 8 dias atraz mas nao queria q aparecesse o dia actual
    tenho usado estes commandos :
    DATEPART(week, dvl.DATa) = DATEPART(week, GETDATE()) AND
    DATEPART(year, dvl.DATa) = DATEPART(year, GETDATE())
    e este :
    dvl.data > = getdate () -8

  97. Pingback: SQLAuthority News – 1200th Post – An Important Milestone Journey to SQL Authority with Pinal Dave

  98. below query returns no rows
    select max(hmp_effectivefrom) from mos_HandlingMarkupPercentage with(nolock) where
    convert(varchar(10),hmp_effectivefrom,101)<=convert(varchar(10),getutcdate(),101) and hmp_Discontinued=0
    group by hmp_EntityId

    i am using convert(varchar(10),getutcdate(),101) format

    below query returns the rows what is the diff between both
    select max(hmp_effectivefrom) from mos_HandlingMarkupPercentage with(nolock) where
    convert(varchar(10),hmp_effectivefrom,102)<=convert(varchar(10),getutcdate(),102) and hmp_Discontinued=0
    group by hmp_EntityId

    i am using convert(varchar(10),getutcdate(),102) format

    • Have you tried this?

      select max(hmp_effectivefrom) from mos_HandlingMarkupPercentage with(nolock)
      where
      hmp_effectivefrom<=dateadd(day,datediff(day,0,getutcdate()),0) and hmp_Discontinued=0
      group by hmp_EntityId

  99. Hello M.Kamala,

    Here you are converting two date values as strings. And strings are compared from left to right character by character. To explain I am taking two values as below:
    hmp_effectivefrom = 10/20/2009
    GetUTCDate() = 01/4/2010

    Now when we compare these values using convert(varchar(10),getutcdate(),101) format, they are compared as:

    10/20/2009 <= 01/04/2010

    This is false and will return record.

    When we convert these two values using convert(varchar(10),getutcdate(),102) format, they are compared as:

    2009/10/20 <= 2010/01/04

    This is true and record will be returned.

    Regards,
    Pinal Dave

    • dear pinal,

      which can i use in my project? any other solution for the same.

      currently i using convert(varchar(10),getutcdate(),101) it’s working 2009 records now 2010 then doesn’t return any record.,

      Pls suggest me if any good idea for date filter

  100. please suggest, can i use below format query?

    select max(hmp_effectivefrom) from mos_HandlingMarkupPercentage with(nolock) where
    cast(convert(varchar(10),hmp_effectivefrom,101) as datetime)<=convert(varchar(10),getutcdate(),101) and hmp_Discontinued=0
    group by hmp_EntityId

  101. otherwise using below l

    select max(hmp_effectivefrom) from mos_HandlingMarkupPercentage with(nolock) where
    hmp_effectivefrom<=convert(varchar(10),getutcdate(),101) and hmp_Discontinued=0
    group by hmp_EntityId

    • thks pinal.

      one query.

      hmp_effectivefrom stored in future value also.

      Ex:

      date stored in table already

      ‘2010-01-05 04:35:09.680′ like this but i run the report before 4:33 then report didn’t pick up the this date. this is my problem.
      ‘2010-01-05 04:33:01.197′ (sysdate)

      Ex:

      hmp_effectivefrom<=getutcdate()
      '2010-01-05 04:35:09.680'<='2010-01-05 04:33:01.197'

      then this record is not displayed. but i want today all records.

      for that i am using like below is it correct or not. have look

      cast(convert(varchar(10),hmp_effectivefrom,101) as datetime)<=convert(varchar(10),getutcdate(),101)

      Please help as soon as possible.

  102. if ok means any difference between below
    cast(convert(varchar(10),hmp_effectivefrom,102) as datetime)<=convert(varchar(10),getutcdate(),102)

    cast(convert(varchar(10),hmp_effectivefrom,112) as datetime)<=convert(varchar(10),getutcdate(),112)

  103. thks pinal.

    one query.

    hmp_effectivefrom stored in future value also.

    Ex:

    date stored in table already

    ‘2010-01-05 04:35:09.680′ like this but i run the report before 4:33 then report didn’t pick up the this date. this is my problem.
    ‘2010-01-05 04:33:01.197′ (sysdate)

    Ex:

    hmp_effectivefrom<=getutcdate()
    '2010-01-05 04:35:09.680'<='2010-01-05 04:33:01.197'

    then this record is not displayed. but i want today all records.

    for that i am using like below is it correct or not. have look

    cast(convert(varchar(10),hmp_effectivefrom,101) as datetime)<=convert(varchar(10),getutcdate(),101)

    i expecting your solution.

  104. I would like to get the following comparison:
    example 1: I would like to receive data 2 years back from current date.

    How can I arange that?

    Thanx!

    Rob (The Netherlands)

  105. Hi Jayan,

    We can not use special characters for file/folder name. “:” is one of special character, so we can not give file name that you want.

    What alternate is give file name like:

    Test_20100112.bak

    where 2010=year, 01=month, 12=date.

    Thanks,

    Tejas
    SQLYoga.com

  106. Hi Pinal,

    I am just a beginner to SQL server 2008 and learning sql commands, i am unable to get logic behand store procedures
    could you please help me out and can you post me the syntax for getting last and fist day of the month if we pass current date using store procedures.

    thank you.

  107. Hi Pinal,

    I am new in SQL Server 2008.

    I want to execute some stored procedure on particular dates(all column entries as dates).

    How shd I do tht?

    Please help me out.

    Thanks.
    Chetan

  108. Hello Chetan,

    What I understand is that you have table containing dates and you want to execute some stored procedure on that dates. If that is what you want to do then create a SQL Server Agent job to execute once daily. In the job check for the GETDDATE() in dates stored in table, if found then execute else skip it.

    Regards,
    Pinal Dave

  109. hi pinal,

    im having trouble inserting and retrieving dates. im using visual web developer 2008 and sql server 2005.
    i would like to insert current date while in retrieving, i want to retrieve date(s) only.
    can you help me out by showing me the format in query?

    thanks!

  110. Hi All,
    I have the column called created date , i want to make the report, for every three month ,like”jan,feb,march”,….etc..,

    when i use between operators i am not able to get , similarly ,
    can u please help me in code ,

    Regards
    sateesh

  111. Hi

    when i tried to find record which is greater than current date it show me all date record because it compare date with time thats why it give wrong record but i tried below which give me right record

    string currentdate = DateTime.Parse(DateTime.Now.ToString()).ToShortDateString();

    select * from tbltest where TrainingDate>='” + currentdate + “‘”

  112. Is it possible to insert on column with date fields that insert automatical after other registry on the same table was inserted?

    Regards,

  113. Pinal,

    I have a table which stores daily transactions and I’m trying to generate a report that captures only transactions less than two weeks from the run date of the report and using ztransactiondate = GETDATE()-14 which is not working to get my results. Any suggestions,

    Thanks,
    Raul

  114. Hello sir…
    Good Morning…
    I want to Display Birthdate of My Site User On My Home Page
    That Information Give ago 3 Days

    Example
    My Client Name Is :- Sachin
    His Birth DAte :- 05-12-1987
    Today Date Is :- 3-12-2009

    So, Today On My Home Page Display “Sachin” Name Nad His BirthDate…

    Thanks

    Regards
    [Removed phone and mobile number]

  115. – SQL Server T-SQL date and datetime formats – sql date / datetime format
    — Date time formats – mssql datetime – sql server date formats – sql dates format
    — MSSQL getdate returns current system date and time in standard internal format
    — SQL datetime formats with century (YYYY or CCYY format)- sql time format
    SELECT convert(varchar, getdate(), 100) — mon dd yyyy hh:mmAM (or PM)
    — Oct 2 2010 11:01AM
    SELECT convert(varchar, getdate(), 101) — mm/dd/yyyy – 10/02/2010
    SELECT convert(varchar, getdate(), 102) — yyyy.mm.dd – 2010.10.02
    SELECT convert(varchar, getdate(), 103) — dd/mm/yyyy
    SELECT convert(varchar, getdate(), 104) — dd.mm.yyyy
    SELECT convert(varchar, getdate(), 105) — dd-mm-yyyy
    SELECT convert(varchar, getdate(), 106) — dd mon yyyy
    SELECT convert(varchar, getdate(), 107) — mon dd, yyyy
    SELECT convert(varchar, getdate(), 108) — hh:mm:ss
    SELECT convert(varchar, getdate(), 109) — mon dd yyyy hh:mm:ss:mmmAM (or PM)
    — Oct 2 2010 11:02:44:013AM
    SELECT convert(varchar, getdate(), 110) — mm-dd-yyyy
    SELECT convert(varchar, getdate(), 111) — yyyy/mm/dd
    SELECT convert(varchar, getdate(), 112) — yyyymmdd
    SELECT convert(varchar, getdate(), 113) — dd mon yyyy hh:mm:ss:mmm
    — 02 Oct 2010 11:02:07:577
    SELECT convert(varchar, getdate(), 114) — hh:mm:ss:mmm(24h)
    SELECT convert(varchar, getdate(), 120) — yyyy-mm-dd hh:mm:ss(24h)
    SELECT convert(varchar, getdate(), 121) — yyyy-mm-dd hh:mm:ss.mmm
    SELECT convert(varchar, getdate(), 126) — yyyy-mm-ddThh:mm:ss.mmm
    — 2010-10-02T10:52:47.513
    — Without century (YY) date / datetime conversion – there are exceptions!
    SELECT convert(varchar, getdate(), 0) — mon dd yyyy hh:mmAM (or PM)
    SELECT convert(varchar, getdate(), 1) — mm/dd/yy
    SELECT convert(varchar, getdate(), 2) — yy.mm.dd
    SELECT convert(varchar, getdate(), 3) — dd/mm/yy
    SELECT convert(varchar, getdate(), 4) — dd.mm.yy
    SELECT convert(varchar, getdate(), 5) — dd-mm-yy
    SELECT convert(varchar, getdate(), 6) — dd mon yy
    SELECT convert(varchar, getdate(), 7) — mon dd, yy
    SELECT convert(varchar, getdate(), 8) — hh:mm:ss
    SELECT convert(varchar, getdate(), 9) — mon dd yyyy hh:mm:ss:mmmAM (or PM)
    SELECT convert(varchar, getdate(), 10) — mm-dd-yy
    SELECT convert(varchar, getdate(), 11) — yy/mm/dd
    SELECT convert(varchar, getdate(), 12) — yymmdd
    SELECT convert(varchar, getdate(), 13) — dd mon yyyy hh:mm:ss:mmm
    SELECT convert(varchar, getdate(), 14) — hh:mm:ss:mmm(24h)
    SELECT convert(varchar, getdate(), 20) — yyyy-mm-dd hh:mm:ss(24h)
    SELECT convert(varchar, getdate(), 21) — yyyy-mm-dd hh:mm:ss.mmm
    SELECT convert(varchar, getdate(), 22) — mm/dd/yy hh:mm:ss AM (or PM)
    SELECT convert(varchar, getdate(), 23) — yyyy-mm-dd
    SELECT convert(varchar, getdate(), 24) — hh:mm:ss
    SELECT convert(varchar, getdate(), 25) — yyyy-mm-dd hh:mm:ss.mmm
    — SQL create different date styles with t-sql string functions
    SELECT replace(convert(varchar, getdate(), 111), ‘/’, ‘ ‘) — yyyy mm dd
    SELECT convert(varchar(7), getdate(), 126) — yyyy-mm
    SELECT right(convert(varchar, getdate(), 106), 8) — mon yyyy
    SELECT substring(convert(varchar, getdate(), 120),6, 11) — mm-dd hh:mm
    ————
    — SQL Server date formatting function – convert datetime to string
    ————
    — SQL datetime functions – SQL date functions – SQL datetime formatting
    — SQL Server date formats – sql server date datetime – sql date formatting
    — T-SQL convert dates – T-SQL date formats – Transact-SQL date formats
    — Formatting dates sql server – sql convert datetime format
    CREATE FUNCTION dbo.fnFormatDate (@Datetime DATETIME, @FormatMask VARCHAR(32))
    RETURNS VARCHAR(32)
    AS
    BEGIN
    DECLARE @StringDate VARCHAR(32)
    SET @StringDate = @FormatMask
    IF (CHARINDEX (‘YYYY’,@StringDate) > 0)
    SET @StringDate = REPLACE(@StringDate, ‘YYYY’, DATENAME(YY, @Datetime))
    IF (CHARINDEX (‘YY’,@StringDate) > 0)
    SET @StringDate = REPLACE(@StringDate, ‘YY’, RIGHT(DATENAME(YY, @Datetime),2))
    IF (CHARINDEX (‘Month’,@StringDate) > 0)
    SET @StringDate = REPLACE(@StringDate, ‘Month’, DATENAME(MM, @Datetime))
    IF (CHARINDEX (‘MON’,@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS)>0)
    SET @StringDate = REPLACE(@StringDate, ‘MON’,
    LEFT(UPPER(DATENAME(MM, @Datetime)),3))
    IF (CHARINDEX (‘Mon’,@StringDate) > 0)
    SET @StringDate = REPLACE(@StringDate, ‘Mon’, LEFT(DATENAME(MM, @Datetime),3))
    IF (CHARINDEX (‘MM’,@StringDate) > 0)
    SET @StringDate = REPLACE(@StringDate, ‘MM’,
    RIGHT(‘0’+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))
    IF (CHARINDEX (‘M’,@StringDate) > 0)
    SET @StringDate = REPLACE(@StringDate, ‘M’,
    CONVERT(VARCHAR,DATEPART(MM, @Datetime)))
    IF (CHARINDEX (‘DD’,@StringDate) > 0)
    SET @StringDate = REPLACE(@StringDate, ‘DD’,
    RIGHT(‘0’+DATENAME(DD, @Datetime),2))
    IF (CHARINDEX (‘D’,@StringDate) > 0)
    SET @StringDate = REPLACE(@StringDate, ‘D’, DATENAME(DD, @Datetime))
    RETURN @StringDate
    END
    GO

    — Microsoft SQL Server date format function test
    — MSSQL formatting dates – sql datetime date
    SELECT dbo.fnFormatDate (getdate(), ‘MM/DD/YYYY’) — 01/03/2012
    SELECT dbo.fnFormatDate (getdate(), ‘DD/MM/YYYY’) — 03/01/2012
    SELECT dbo.fnFormatDate (getdate(), ‘M/DD/YYYY’) — 1/03/2012
    SELECT dbo.fnFormatDate (getdate(), ‘M/D/YYYY’) — 1/3/2012
    SELECT dbo.fnFormatDate (getdate(), ‘M/D/YY’) — 1/3/12
    SELECT dbo.fnFormatDate (getdate(), ‘MM/DD/YY’) — 01/03/12
    SELECT dbo.fnFormatDate (getdate(), ‘MON DD, YYYY’) — JAN 03, 2012
    SELECT dbo.fnFormatDate (getdate(), ‘Mon DD, YYYY’) — Jan 03, 2012
    SELECT dbo.fnFormatDate (getdate(), ‘Month DD, YYYY’) — January 03, 2012
    SELECT dbo.fnFormatDate (getdate(), ‘YYYY/MM/DD’) — 2012/01/03
    SELECT dbo.fnFormatDate (getdate(), ‘YYYYMMDD’) — 20120103
    SELECT dbo.fnFormatDate (getdate(), ‘YYYY-MM-DD’) — 2012-01-03
    — CURRENT_TIMESTAMP returns current system date and time in standard internal format
    SELECT dbo.fnFormatDate (CURRENT_TIMESTAMP,’YY.MM.DD’) — 12.01.03
    GO
    ————

    /***** SELECTED SQL DATE/DATETIME FORMATS WITH NAMES *****/

    — SQL format datetime – – sql hh mm ss – sql yyyy mm dd
    — Default format: Oct 23 2006 10:40AM
    SELECT [Default]=CONVERT(varchar,GETDATE(),100)

    — US-Style format: 10/23/2006
    SELECT [US-Style]=CONVERT(char,GETDATE(),101)

    — ANSI format: 2006.10.23
    SELECT [ANSI]=CONVERT(char,CURRENT_TIMESTAMP,102)

    — UK-Style format: 23/10/2006
    SELECT [UK-Style]=CONVERT(char,GETDATE(),103)

    — German format: 23.10.2006
    SELECT [German]=CONVERT(varchar,GETDATE(),104)

    — ISO format: 20061023
    SELECT ISO=CONVERT(varchar,GETDATE(),112)

    — ISO8601 format: 2010-10-23T19:20:16.003
    SELECT [ISO8601]=CONVERT(varchar,GETDATE(),126)
    ————

    — SQL Server datetime formats – Format dates SQL Server 2005 / 2008
    — Century date format MM/DD/YYYY usage in a query
    SELECT TOP (1)
    SalesOrderID,
    OrderDate = CONVERT(char(10), OrderDate, 101),
    OrderDateTime = OrderDate
    FROM AdventureWorks.Sales.SalesOrderHeader
    /*
    SalesOrderID OrderDate OrderDateTime
    43697 07/01/2001 2001-07-01 00:00:00.000
    */

    — SQL update datetime column – SQL datetime DATEADD – datetime function
    UPDATE Production.Product
    SET ModifiedDate=DATEADD(dd,1, ModifiedDate)
    WHERE ProductID = 1001

    — MM/DD/YY date format – Datetime format sql
    SELECT TOP (1)
    SalesOrderID,
    OrderDate = CONVERT(varchar(8), OrderDate, 1),
    OrderDateTime = OrderDate
    FROM AdventureWorks.Sales.SalesOrderHeader
    ORDER BY SalesOrderID desc
    /*
    SalesOrderID OrderDate OrderDateTime
    75123 07/31/04 2004-07-31 00:00:00.000
    */
    ————

    — SQL convert datetime to char – sql date string concatenation: + (plus) operator
    PRINT ‘Style 110: ‘+CONVERT(CHAR(10),GETDATE(),110) — Style 110: 07-10-2012
    PRINT ‘Style 111: ‘+CONVERT(CHAR(10),GETDATE(),111) — Style 111: 2012/07/10
    PRINT ‘Style 112: ‘+CONVERT(CHAR(8), GETDATE(),112) — Style 112: 20120710
    ————
    — Combining different style formats for date & time
    — Datetime formats – sql times format – datetime formats sql
    DECLARE @Date DATETIME
    SET @Date = ‘2015-12-22 03:51 PM’
    SELECT CONVERT(CHAR(10),@Date,110) + SUBSTRING(CONVERT(varchar,@Date,0),12,8)
    — Result: 12-22-2015 3:51PM

    — Microsoft SQL Server cast datetime to string
    SELECT stringDateTime=CAST (getdate() as varchar)
    — Result: Dec 29 2012 3:47AM
    ————
    — SQL Server date and time functions overview
    ————
    — SQL Server CURRENT_TIMESTAMP function
    — SQL Server datetime functions
    — local NYC – EST – Eastern Standard Time zone
    — SQL DATEADD function – SQL DATEDIFF function
    SELECT CURRENT_TIMESTAMP — 2012-01-05 07:02:10.577
    — SQL Server DATEADD function
    SELECT DATEADD(month,2,’2012-12-09′) — 2013-02-09 00:00:00.000
    — SQL Server DATEDIFF function
    SELECT DATEDIFF(day,’2012-12-09′,’2013-02-09′) — 62
    — SQL Server DATENAME function
    SELECT DATENAME(month, ‘2012-12-09′) — December
    SELECT DATENAME(weekday, ‘2012-12-09′) — Sunday
    — SQL Server DATEPART function
    SELECT DATEPART(month, ‘2012-12-09′) — 12
    — SQL Server DAY function
    SELECT DAY(‘2012-12-09′) — 9
    — SQL Server GETDATE function
    — local NYC – EST – Eastern Standard Time zone
    SELECT GETDATE() — 2012-01-05 07:02:10.577
    — SQL Server GETUTCDATE function
    — London – Greenwich Mean Time
    SELECT GETUTCDATE() — 2012-01-05 12:02:10.577
    — SQL Server MONTH function
    SELECT MONTH(‘2012-12-09′) — 12
    — SQL Server YEAR function
    SELECT YEAR(‘2012-12-09′) — 2012

    ————
    — T-SQL Date and time function application
    — CURRENT_TIMESTAMP and getdate() are the same in T-SQL
    ————
    — T-SQL first day of week and last day of week
    SELECT FirstDateOfWeek = dateadd(dd,-DATEPART(dw,GETDATE()) + 1,GETDATE())
    SELECT LastDateOfWeek = dateadd(dd,7 – DATEPART(dw,GETDATE()),GETDATE())
    — SQL first day of the month
    — SQL first date of the month
    — SQL first day of current month – 2012-01-01 00:00:00.000
    SELECT DATEADD(dd,0,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))
    — SQL last day of the month
    — SQL last date of the month
    — SQL last day of current month – 2012-01-31 00:00:00.000
    SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP)+1,0))
    — SQL first day of last month
    — SQL first day of previous month – 2011-12-01 00:00:00.000
    SELECT DATEADD(mm,-1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))
    — SQL last day of last month
    — SQL last day of previous month – 2011-12-31 00:00:00.000
    SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,DATEADD(MM,-1,GETDATE()))+1,0))
    — SQL first day of next month – 2012-02-01 00:00:00.000
    SELECT DATEADD(mm,1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))
    — SQL last day of next month – 2012-02-28 00:00:00.000
    SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,DATEADD(MM,1,GETDATE()))+1,0))
    GO
    — SQL first day of a month – 2012-10-01 00:00:00.000
    DECLARE @Date datetime; SET @Date = ‘2012-10-23′
    SELECT DATEADD(dd,0,DATEADD(mm, DATEDIFF(mm,0,@Date),0))
    GO
    — SQL last day of a month – 2012-03-31 00:00:00.000
    DECLARE @Date datetime; SET @Date = ‘2012-03-15′
    SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,@Date)+1,0))
    GO
    — SQL first day of year
    — SQL first day of the year – 2012-01-01 00:00:00.000
    SELECT DATEADD(yy, DATEDIFF(yy,0,CURRENT_TIMESTAMP), 0)
    — SQL last day of year
    — SQL last day of the year – 2012-12-31 00:00:00.000
    SELECT DATEADD(yy,1, DATEADD(dd, -1, DATEADD(yy,
    DATEDIFF(yy,0,CURRENT_TIMESTAMP), 0)))
    — SQL last day of last year
    — SQL last day of previous year – 2011-12-31 00:00:00.000
    SELECT DATEADD(dd,-1,DATEADD(yy,DATEDIFF(yy,0,CURRENT_TIMESTAMP), 0))
    GO
    — SQL calculate age in years, months, days – Format dates SQL Server 2008
    — SQL table-valued function – SQL user-defined function – UDF
    — SQL Server age calculation – date difference
    USE AdventureWorks2008;
    GO
    CREATE FUNCTION fnAge (@BirthDate DATETIME)
    RETURNS @Age TABLE(Years INT,
    Months INT,
    Days INT)
    AS
    BEGIN
    DECLARE @EndDate DATETIME, @Anniversary DATETIME
    SET @EndDate = Getdate()
    SET @Anniversary = Dateadd(yy,Datediff(yy,@BirthDate,@EndDate),@BirthDate)
    INSERT @Age
    SELECT Datediff(yy,@BirthDate,@EndDate) – (CASE
    WHEN @Anniversary > @EndDate THEN 1
    ELSE 0
    END), 0, 0
    UPDATE @Age SET Months = Month(@EndDate – @Anniversary) – 1
    UPDATE @Age SET Days = Day(@EndDate – @Anniversary) – 1
    RETURN
    END
    GO

    — Test table-valued UDF
    SELECT * FROM fnAge(‘1956-10-23′)
    SELECT * FROM dbo.fnAge(‘1956-10-23′)
    /* Results
    Years Months Days
    52 4 1
    */

    ———-
    — SQL date range between
    ———-
    — SQL between dates
    USE AdventureWorks;
    — SQL between
    SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader
    WHERE OrderDate BETWEEN ‘20040301’ AND ‘20040315’
    — Result: 108

    — BETWEEN operator is equivalent to >=…AND….= ‘20040301’ AND OrderDate < '20040316'

    — SQL between with DATE type (SQL Server 2008)
    SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader
    WHERE CONVERT(DATE, OrderDate) BETWEEN '20040301' AND '20040315'
    ———-
    — Non-standard format conversion: 2011 December 14
    — SQL datetime to string
    SELECT [YYYY Month DD] =
    CAST(YEAR(GETDATE()) AS VARCHAR(4))+ ' '+
    DATENAME(MM, GETDATE()) + ' ' +
    CAST(DAY(GETDATE()) AS VARCHAR(2))

    — Converting datetime to YYYYMMDDHHMMSS format: 20121214172638
    SELECT replace(convert(varchar, getdate(),111),'/','') +
    replace(convert(varchar, getdate(),108),':','')

    — Datetime custom format conversion to YYYY_MM_DD
    select CurrentDate=rtrim(year(getdate())) + '_' +
    right('0' + rtrim(month(getdate())),2) + '_' +
    right('0' + rtrim(day(getdate())),2)

    — Converting seconds to HH:MM:SS format
    declare @Seconds int
    set @Seconds = 10000
    select TimeSpan=right('0' +rtrim(@Seconds / 3600),2) + ':' +
    right('0' + rtrim((@Seconds % 3600) / 60),2) + ':' +
    right('0' + rtrim(@Seconds % 60),2)
    — Result: 02:46:40

    — Test result
    select 2*3600 + 46*60 + 40
    — Result: 10000
    — Set the time portion of a datetime value to 00:00:00.000
    — SQL strip time from date
    — SQL strip time from datetime
    SELECT CURRENT_TIMESTAMP ,DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP), 0)
    — Results: 2014-01-23 05:35:52.793 2014-01-23 00:00:00.000
    /* VALID DATE RANGES FOR DATE/DATETIME DATA TYPES

    SMALLDATETIME (4 bytes) date range:
    January 1, 1900 through June 6, 2079

    DATETIME (8 bytes) date range:
    January 1, 1753 through December 31, 9999

    DATETIME2 (8 bytes) date range (SQL Server 2008):
    January 1,1 AD through December 31, 9999 AD

    DATE (3 bytes) date range (SQL Server 2008):
    January 1, 1 AD through December 31, 9999 AD

    *******/
    — Selecting with CONVERT into different styles
    — Note: Only Japan & ISO styles can be used in ORDER BY
    SELECT TOP(1)
    Italy = CONVERT(varchar, OrderDate, 105)
    , USA = CONVERT(varchar, OrderDate, 110)
    , Japan = CONVERT(varchar, OrderDate, 111)
    , ISO = CONVERT(varchar, OrderDate, 112)
    FROM AdventureWorks.Purchasing.PurchaseOrderHeader
    ORDER BY PurchaseOrderID DESC
    /* Results
    Italy USA Japan ISO
    25-07-2004 07-25-2004 2004/07/25 20040725
    */
    — SQL Server convert date to integer
    DECLARE @Datetime datetime
    SET @Datetime = '2012-10-23 10:21:05.345'
    SELECT DateAsInteger = CAST (CONVERT(varchar,@Datetime,112) as INT)
    — Result: 20121023

    — SQL Server convert integer to datetime
    DECLARE @intDate int
    SET @intDate = 20120315
    SELECT IntegerToDatetime = CAST(CAST(@intDate as varchar) as datetime)
    — Result: 2012-03-15 00:00:00.000
    ————
    — SQL Server CONVERT script applying table INSERT/UPDATE
    ————
    — SQL Server convert date
    — Datetime column is converted into date only string column
    USE tempdb;
    GO
    CREATE TABLE sqlConvertDateTime (
    DatetimeCol datetime,
    DateCol char(8));
    INSERT sqlConvertDateTime (DatetimeCol) SELECT GETDATE()

    UPDATE sqlConvertDateTime
    SET DateCol = CONVERT(char(10), DatetimeCol, 112)
    SELECT * FROM sqlConvertDateTime

    — SQL Server convert datetime
    — The string date column is converted into datetime column
    UPDATE sqlConvertDateTime
    SET DatetimeCol = CONVERT(Datetime, DateCol, 112)
    SELECT * FROM sqlConvertDateTime

    — Adding a day to the converted datetime column with DATEADD
    UPDATE sqlConvertDateTime
    SET DatetimeCol = DATEADD(day, 1, CONVERT(Datetime, DateCol, 112))
    SELECT * FROM sqlConvertDateTime

    — Equivalent formulation
    — SQL Server cast datetime
    UPDATE sqlConvertDateTime
    SET DatetimeCol = DATEADD(dd, 1, CAST(DateCol AS datetime))
    SELECT * FROM sqlConvertDateTime
    GO
    DROP TABLE sqlConvertDateTime
    GO
    /* First results
    DatetimeCol DateCol
    2014-12-25 16:04:15.373 20141225 */

    /* Second results:
    DatetimeCol DateCol
    2014-12-25 00:00:00.000 20141225 */

    /* Third results:
    DatetimeCol DateCol
    2014-12-26 00:00:00.000 20141225 */
    ————
    — SQL month sequence – SQL date sequence generation with table variable
    — SQL Server cast string to datetime – SQL Server cast datetime to string
    — SQL Server insert default values method
    DECLARE @Sequence table (Sequence int identity(1,1))
    DECLARE @i int; SET @i = 0
    DECLARE @StartDate datetime;
    SET @StartDate = CAST(CONVERT(varchar, year(getdate()))+
    RIGHT('0'+convert(varchar,month(getdate())),2) + '01' AS DATETIME)
    WHILE ( @i = ‘1997-11-01′ AND
    RateChangeDate = ‘1997-11-01 00:00:00′ AND
    RateChangeDate < '1998-01-06 00:00:00'
    GO
    */
    ————
    — SQL datetime language setting
    — SQL Nondeterministic function usage – result varies with language settings
    SET LANGUAGE 'us_english'; –– Jan 12 2015 12:00AM
    SELECT US = convert(VARCHAR,convert(DATETIME,'01/12/2015'));
    SET LANGUAGE 'British'; –– Dec 1 2015 12:00AM
    SELECT UK = convert(VARCHAR,convert(DATETIME,'01/12/2015'));
    SET LANGUAGE 'German'; –– Dez 1 2015 12:00AM
    SET LANGUAGE 'Deutsch'; –– Dez 1 2015 12:00AM
    SELECT Germany = convert(VARCHAR,convert(DATETIME,'01/12/2015'));
    SET LANGUAGE 'French'; –– déc 1 2015 12:00AM
    SELECT France = convert(VARCHAR,convert(DATETIME,'01/12/2015'));
    SET LANGUAGE 'Spanish'; –– Dic 1 2015 12:00AM
    SELECT Spain = convert(VARCHAR,convert(DATETIME,'01/12/2015'));
    SET LANGUAGE 'Hungarian'; –– jan 12 2015 12:00AM
    SELECT Hungary = convert(VARCHAR,convert(DATETIME,'01/12/2015'));
    SET LANGUAGE 'us_english';
    GO
    ————
    — SQL Server 2008 T-SQL find next Monday for a given date
    DECLARE @DateTime DATETIME = '2012-12-31'
    SELECT NextMondaysDate=DATEADD(dd,(DATEDIFF(dd, 0, @DateTime) / 7 * 7) + 7, 0),
    WeekDayName=DATENAME(dw,DATEADD(dd,(DATEDIFF(dd, 0, @DateTime) / 7 * 7) + 7, 0));
    /*
    NextMondaysDate WeekDayName
    2013-01-07 00:00:00.000 Monday
    */
    ————
    ————
    — Function for Monday dates calculation
    ————
    USE AdventureWorks2008;
    GO
    — SQL user-defined function
    — SQL scalar function – UDF
    CREATE FUNCTION fnMondayDate
    (@Year INT,
    @Month INT,
    @MondayOrdinal INT)
    RETURNS DATETIME
    AS
    BEGIN
    DECLARE @FirstDayOfMonth CHAR(10),
    @SeedDate CHAR(10)

    SET @FirstDayOfMonth = convert(VARCHAR,@Year) + '-' + convert(VARCHAR,@Month) + '-01'
    SET @SeedDate = '1900-01-01'

    RETURN DATEADD(DD,DATEDIFF(DD,@SeedDate,DATEADD(DD,(@MondayOrdinal * 7) – 1,
    @FirstDayOfMonth)) / 7 * 7, @SeedDate)
    END
    GO

    — Test Datetime UDF
    — Third Monday in Feb, 2015
    SELECT dbo.fnMondayDate(2016,2,3)
    — 2015-02-16 00:00:00.000

    — First Monday of current month
    SELECT dbo.fnMondayDate(Year(getdate()),Month(getdate()),1)
    — 2009-02-02 00:00:00.000
    ————

  116. Hi Pinal,

    How can i fetch the current datetime using user defined function? I think we may not user getdate() function with in the user defined function.thanks in advance.

  117. Hello Pinal,

    I want to write a function for daylight saving. can you tell me how to find out last sunday of month in sql 2000.

    Regard,
    Ajay

    • @Ajay

      SELECT
      DATEADD
      (
      d,
      CASE DATEPART(dw, Next_Month.Date)
      WHEN 1 THEN 7
      ELSE DATEPART(dw, Next_Month.Date) – 1
      END * -1,
      Next_Month.Date
      )
      FROM
      (SELECT DATEADD(m, DATEDIFF(m, 0, GETDATE()) + 1, 0) Date) Next_Month;

  118. i inserted 400 records in to sql server 2008 in particular day. after some day i realised that inserted data was wrong. how to delete that data.

    • @SIVA NANDA REDDY

      If you have a date in the data

      DELETE FROM table
      WHERE date-col >= DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0)
      AND date-col < DATEADD(d, DATEDIFF(d, 0, GETDATE()) + 1, 0);

  119. hello sir i am working on SQL that is on ORACLE server ….i want to insert current date and time in one of my tables..what should be its format…i tried the following..but htis is only giving me current system date..

    insert into appliance_reading values(to_date(sysdate,’DD-MON-YYYY:HH12:MI:SSAM’));

    please correct the following……and ur info is really valuable…thank u sir!

  120. Hi ! i am new to this but i have a doubt regarding sql dates.
    i am getting an old date from database and i have to change only the date to current date. i am doing this in java but facing problems with java.sql.date and java.sql.date.
    Please help me!!

  121. Dear All,

    Hi i need a little help from u guys… we have an application where client wants to stop there users acess the application after 7.30 pm just they need to open the appication but they should not able to login the application untill admin gives permission……this is where i got struck kindly help me out guys

  122. Hi Everyone,

    This is part of select statement query in Access and I am trying to replicate in SQL 2008 server and i am really struggling, Please can someone help

    Here is the query.

    Select fname,lname,
    DateSerial(2010,[Month],[Day])-[S Date] AS [Days Late],
    IIf((DateSerial(2010,[Month],[Day])-[S Date]<=14),"1","0") AS [0 - 2 Week Marker],
    IIf((DateSerial(2010,[Month],[Day])-[S Date]14),”1″,”0″) AS [2 - 4 Week Marker],
    IIf((DateSerial(2010,[Month],[Day])-[S Date]28),”1″,”0″) AS [4 - 6 Week Marker],
    IIf((DateSerial(2010,[Month],[Day])-[S Date]42),”1″,”0″) AS [6 - 13 Week Marker],
    IIf((DateSerial(2010,[Month],[Day])-[S Date]>91),”1″,”0″) AS [13 Week Marker]
    from Tickets
    Where
    ((DateSerial(2010,[Month],[Day])-[S Date])>14))
    ORDER BY DateSerial(2010,[Month],[Day])-[S Date] DESC , IIf((DateSerial(2010,[Month],[Day])-[S Date]<=14),"1","0");

    • Hi Sohail,

      Sorry I haven’t the time to look at the above at the moment, but to point you in the (hopefully) right direction, you are going to want to use a CASE statement and the CONVERT function.

      Gareth.

  123. how to find a day,hours and minutes from one query.
    guys i want the OutPut Like this
    as i’m selecting a query which have many crows i’m showing some sampl that i want

    Days
    ————-
    1 Days ago
    8 hours ago
    12 minutes ago
    2 minutes ago
    8 minutes ago
    12 hours ago

    please help me

    • select
      case when datediff(day, MyDate, getdate())>=1
      then cast(datediff(day, MyDate, getdate())as varchar )+’ day(s) ago’
      else
      case when datediff(hour, MyDate, getdate())>=1
      then cast(datediff(hour, MyDate, getdate())as varchar )+’ hour(s) ago’
      else
      case when datediff(minute, MyDate, getdate())>=1
      then cast(datediff(minute, MyDate, getdate())as varchar )+’ minute(s) ago’
      else ”
      end
      end
      end
      from MyTable

      You need something like this?

  124. Dear All,

    Hi i need help from u,i want to display a current date on my web application but it shows previous date upto 12.30 PM, after 12.30 PM as indian time its get change to current date .Please help me how to solve this problem.

  125. Hi,
    I have a table with 2 columns, one for date(only) and one for time(only).
    I need to insert into those columns the current date and the current time.
    Could you help me please?

  126. I am looking for function that returns the data between two times
    for ex. mod_date_time between ’2010-07-21′ and ’2010-07-22′ will give me the data which has 21/07/2010 date.

    But if I want to see the data between 06:30 am to 09:00am, How do i do this?

    Please help me out, with this one!!!

  127. Hi Vikash,

    You can use to write a query as

    SELECT *
    FROM Table
    WHERE DATE BETWEEN ‘2010-07-21 06:30′ AND ‘2010-07-21 09:30′

    This query will return only data between 06:30 AM and 09:30 for 2010-07-21

    Thanks,
    Tejas

  128. how to have the query for data enter in one particular month and year like range 1st of every month to 5th of same month, 6th to 10th, 11th to 15th like wise

  129. pls i have having serious problem with my application.whenever any transaction takes place,it records the date the transaction took place in this format,04/08/2010(mm,dd,yyyy).from the client end they are suppose to be able to view report for all transaction ,but the problem is that the rad date picker brings out the date in this format
    4/8/2010 12:00am but i was able to use this code RadDatePicker1.SelectedDate.Value.ToShortDateString(); to get only the date.what i need is for the date value gotten from this rad date picker to be in the same format with the first one ,that is 04/08/2010 must be in the same format as 4/8/2010. i have tried using SELECT CONVERT(CHAR(10),4/8/2010,110) it didnt work.what sql code do i use.

  130. I have 3 columns in a table(CreationDate, LastModifiedDate, presentDate). when i am inserting data into record i want system datetime automatically updated to the table.

    how can i do that

  131. Hi all,

    i have a ITEM_ID,start date & end date. For the ITEM_ID, if the current date falls under the start date & end date ,it should return yes or else no.

    How to achieve this?

    Thanks in Advance

    Thanks & Regards,
    Priya

  132. Hi All,

    I want to convert MM-YY (for ex: 04-99) to YYYY-MM-01 (always first day of the month ex: 1999-04-01). Could anyone please advise?

    Thanks in advance,
    Sathish.

  133. – =============================================================================
    — Author : Venkata Narayana Thogati
    — Create date : July 26 -2010
    — Description : This SP for to get the day dates of perticular day in a month
    — Parameters : @month, @year, @dayname
    — Exicution : Exec [p_GetReqWeekDaysInAMonth] 01, 2010, ‘Monday’
    — Result : It givies the all monday dates in jan month of 2010 year
    — =============================================================================
    ALTER PROCEDURE [dbo].[p_GetReqWeekDaysInAMonth]
    — Add the parameters for the stored procedure here
    @month int = 0,
    @year int = 0,
    @dayname varchar(50)
    AS
    BEGIN
    — SET NOCOUNT ON added to prevent extra result sets from
    SET NOCOUNT ON;

    SELECT
    CAST(CAST(@year AS VARCHAR) + ‘-‘ + CAST(@Month AS VARCHAR) + ‘-01′ AS DATETIME) + Number as DaysDate,
    DATENAME(dw,CAST(CAST(@year AS VARCHAR) + ‘-‘ + CAST(@Month AS VARCHAR) + ‘-01′ AS DATETIME) + Number) as DaysName
    FROM master..spt_values
    WHERE
    TYPE = ‘P’
    AND
    (DATENAME(dw,CAST(CAST(@year AS VARCHAR) + ‘-‘ + CAST(@Month AS VARCHAR) + ‘-01′ AS DATETIME) + Number) = @dayname)
    AND
    (CAST(CAST(@year AS VARCHAR) + ‘-‘ + CAST(@Month AS VARCHAR) + ‘-01′ AS DATETIME) + Number )
    <
    DATEADD(mm,1,CAST(CAST(@year AS VARCHAR) + '-' + CAST(@Month AS VARCHAR) + '-01' AS DATETIME) )

    SET NOCOUNT OFF;
    END

  134. Hai,

    I get current time using the following select query,
    SELECT RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100),7)
    Result : 4:00PM
    SELECT RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100),7)
    Result : 5:20PM
    I want the time difference and get result like this 01:20 using select query, plz tell anyone what sql query?

  135. hi i am a sap b1 developer.i entered time in a field.when i am retrieving the time when 00:01 it is showing as “1” please tell me how to retrieve time value.

  136. Hi All…

    Ho can I convert sum of decimal to time
    actually data in data base as fallows:

    Col1 Col2 Col3
    1.20 1.30 1.20

    I tried to Sum(col1+col2+col3) = 3.70

    but here 1.20 = 1hr 20min, 1.30 = 1hr 30min

    I want result should be as 4.10, for this result what can I do?

    please advice is there any userdefined function is there for this

    Thanks,
    Venkat919

  137. hello sir

    getdate result ime and date show

    select getdate as currenttime
    time and date will be show

    but i have a one problem in create table
    create table employee
    (
    emp_id int,
    emp_name varchar(30),
    emp_time datetime default ‘getdate()’
    );

    insert into employee tables(01,’happy’,”);
    but output is different emp_time 1900-01-01
    i want to say that default date and time come in emp_time
    plz sir help me about this query
    by

  138. How to get difference between two times for example :

    ’28/10/2010 00:05:45′ and ’28/10/2010 00:07:50′

    i want to know how to get difference between only time ???

    answer must be in the format : ’00:02:05′

  139. How to retrieve current datetime “2/16/2008 12:15:12 PM” from sql query “SELECT GETDATE()” will come “2010-10-21 15:25:31.437″

    I need below format.

    “2/16/2008 12:15:12 PM”

    Can any one please help

  140. Performance:
    There is absolutely no difference in using any of them. As they are absolutely same.

    Very wrong. You said yourself, getdate() is a nondeterministic function and cannot be indexed in views.

    It makes a big difference in views with large tables, where you use the date in a where clause to filter for a reporting date.

    SELECT {fn NOW()}
    is the one you should choose, and getdate() is the one you should avoid.

  141. But this is very wrong.

    You wrote yourself:
    GETDATE is a nondeterministic function. Views and expressions that reference this column cannot be indexed.

    So if you have a view with a few joins, how do you think getdate() will influence the performance. I can tell you: getdate()’s performance is so very bad, you are a lot faster using the canonic ODBC escape sequence functions, such as fn now() …

  142. dear sir , i m software trainee , i inserted system date and time in table but i cant update that systemdate and time , i cant identify problem please give me a solutions..

    what query used in sql server to update?

  143. I have a Sql Sever 2008 DB with a list of dates and events associated with those dates. I’d like to return only those events that are happening on the current date.

    Something like
    SELECT [event] FROM [new database] WHERE ([date] = @currentdate) ..I know this isn’t right.
    Any advise would be appreciated

  144. Hi Nadeeka

    If u want get ‘creation date’ , ‘modification date’ for all tables then , u can use this …….
    …………………………………………………………….
    Select name as ‘Table Name’, create_date as ‘Creation Date’, modify_date as ‘Modification Date’ from sys.objects

    where type_desc= ‘USER_TABLE’
    ………………………………………………………….

    Satender

  145. i have a sql server 2005 database.
    on which a table cntains a filed datetime.
    now i need to fetch value filtered by date. but when i am executing query, it returns NULL. i think sql server stores date as datetime field, thats why i am not getting actual resuls. Please help what i will do.

    Indranil

  146. can you help me if there is any Facility or command in SQL Server 2005 ..
    to Execute the stored procedure on particular time span..

    I want to execute stored procedure /Function @ 00:01AM (Automatically) on daily basis..

    i m using ASP.Net

    I m fresher …Plz kindly tell me if possible..

  147. Dear Sir,
    I create a login form in which if you insert password 3 times wrong then u r account is locked for next 15 minute.
    and we send a random password on referenced emailid which is sent after 15 minute.
    For these 15 minute the account isactive=’false’.

    So can u please help me to give code for a stored procedure or triggers which is fired after 15 minute,which update this account isactive = ‘true’.

    Please help me.

    Thanks In Advance

  148. Skaters take tests to quantify advancement in their chosen discipline. Upon completion of each of these exams, a congratulatory email will be sent to the skater to acknowledge his or her achievement.
    Test data is stored in a database; this application will run daily. It will query the database for any tests completed the day before and will send emails to those who have completed their exams. It will also send a notification email to a Test Administer listing each of the skaters, the exam completed, and the date.

    Requirements
    Create a windows console application that retrieves data from a database and sends an email upon successful completion of an exam.
    1. Application will be scheduled to run daily. Data retrieved will be from the day previous. (a SQL mdb file is acceptable)
    2. Email body is to be in HTML format. Text is provided below.
    a. Email will greet each skater by name
    b. Email will specifically mention the Exam name
    c. Email will indicate the date on which the skater passed the exam
    d. Email will be ‘signed’ by the developer (See Text section below)
    3. Administrator notification email body is to be in HTML.
    4. Application must demonstrate at least two different exams (exam names)
    5. Application will contain at least one user defined class. It is not acceptable to demonstrate the concept within the default Program class.
    6. While demonstration of ability to send the email from the application is mandatory in lieu of actually sending an email, application will output to the console.
    7. Application source will be sent in a compressed file to the person delivering these requirements for evaluation. Time spent in development should be included in the message or with the project.
    need help with this. Thank you

  149. I have a Special Offer Product table in this one Product name,
    Offer Start date and Offer End date having column names

    how to write a query to
    when User enter a date

    to get the Product Name when that date is must should between Start date and End date of that table

    Please tell me how write the query the above the condition?

  150. my application is host in USA means the server is in USA but i want datetime of client machine. my client can be anywhere in world. it is return datetime of server but i want client machine datetime. please reply me……

  151. hai ,
    how can i get the date beyond the limit(i guess the limit is around 1753 ) that had been defined by the sql. and that should be useful for different functionalities .

  152. “ALTER SESSION SET TIME_ZONE” is in oracle. what is the command
    SQlserver
    for changing the timezone from EST to IST

  153. HI PINAL

    “ALTER SESSION SET TIME_ZONE” is in oracle. what is the command IN

    SQlserver
    for changing the timezone from EST to IST

  154. Hello Every one

    How can i display Date in

    20th July 2011 form in SQL SERVER

    i know in oracle we used date format

    DDth MON YYYY

    Don;t know how to do in SQL SERVER 2008 .

    Regards

    Qazi

  155. I am storing Created date and time in all the tables. The problem is that webserver and sql server is in US and it is storing US date time as the server is in US. How we will get indian date and time corresponding to US date and time

  156. i have a table1 with columns empid ,name,age and other table2 with columns empid ,salary ,here in table2 empid used as foreign key
    so pls anybody give me solution to find the highest salary of the employee

  157. want to know how can I get the YEAR,Month into database.. example .Suppose it’s August, 2011 now the output that i need is like this: CAB 11 08 001 .. (CAB + YEAR + MONTH + CURRENT NO. in tracking number. )

    this is my sql ..

    –ALTER PROCEDURE [dbo].[generateTrackNo] AS –Declare @tempYear VARCHAR(5),@tempMonth VARCHAR(5) –Set @tempYear = Year(GetDate()) –Set @tempMonth = Month(GetDate()) –SELECT ‘CAB’ + Right(Cast(Year(GetDate()) as varchar(10)),2)+ Right(Cast(Month(GetDate()) as varchar(10)),2) + Right(‘000000’+ Cast(CurrentNo as varchar(10)), 5) from tblTrackNo where GenYear = @tempYear –UPDATE tblTrackNo SET CurrentNo = CurrentNo + 1 where GenYear = @tempYear the output for this is CAB1180035 .. i need CAB1108035 .. i need to put Zero(0) 08 like this for Month..

    in my table i have only genYear and Current No. do i need to add another column for MONTH? thank you so much

  158. Hello All
    In oracle i can pass parameters to query in run time like
    select * from dept where deptno =&deptno
    when i run this query it prompts me for deptno at run time
    .
    how do i do this in sql server .

    waiting for reply thanks in advance.
    Yahya

  159. Hi,
    i have a table in which date is a field,this value gets entered once the user specify date in front end, now
    i need to intimate user after 3 months from this date stored?
    how to do it ? Do i need to give separate field for it?Please help

  160. –Getting todays date record

    select * from tablename where datecolumn between ‘2011-10-25 00:00:00′ And ‘2011-10-25 23:59:59′

    • Dont specify the date. It will work work if you run later. The general approcah is

      select * from tablename where datecolumn>=dateadd(day,datediff(day,0,getdate()),0) and datecol<dateadd(day,datediff(day,0,getdate()),1)

  161. FWIW, I was connecting to a non-SQL DB (PROGRESS still lives!) while creating a DTS package through ODBC and it does make a difference regarding which one you use.

    GETDATE() was giving me an error until I tried {fn NOW()} which worked like a charm.

    Thanks to this page, I was able to find a solution though.

  162. I have one datetime variable. It contains the value like {10/10/2011 2:33:33 PM}. How can i compare this value with the other datetime variable’s value which is like {2011-10-10 14:33:33.990} ?

    This both are the same time but still i can’t compare it. Help me how to do it?

    Thanks in advance,
    Yash Thakkar

  163. Hi,

    How Can I Insert / modify current date time in TimeStamp Column.

    when I try to update timestamp column, its giving msg
    Msg 272, Level 16, State 1, Line 1
    Cannot update a timestamp column.

    when i try to insert timestamp column, its giving msg
    Msg 273, Level 16, State 1, Line 1
    Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.

    Thanks in advance,
    Vijay Vegi

  164. hi,i want to know about the date datatype on sql server2005, that didnt acquire the date datatype.

    How can create the date datatype in sql query.

    greantly give me your suggesstion.

    thanks,
    Gomathi

  165. hi,i want to know about the date datatype on sql server2005, that didnt acquire the date datatype.

    How can create the date datatype in sql query.

    greantly give me your suggesstion.

    thanks,
    Gomathi

  166. Hi Pinal,

    I need to calculate the number of days by doing this calculation
    MTRY_DT – (YR_NUM+MO_NUM+Last day of the month) ,Where MTRY_DT = Maturity Date . So,how can I do this for all the rows that I have.
    I have 6446 rows.

    Thanks in Advance,
    Samyuktha

  167. thanks pinal for ur help. can u pls help in some question….is SQL/PLI is similar to ODBC????also to JDBC?????

  168. hi

    i want to fetch the data according to particular time i select. suppose i want to fetch data between date 10 to 15 and for particular hr 16:00 then what is the suitable query for that. kindly help me

  169. hi I am Ram,

    I want to get last one year records …how can i get it..

    i used this following SP. to get it.

    alter procedure dev_GetAllWeekCharts_M
    @userID bigint,
    @StartDate datetime

    as

    BEGIN

    declare @EndDate datetime = dateadd(Year, -1, @StartDate)

    SELECT [weight],convert(varchar,Updateddate,101)[Updateddate] into #tempWeight from dev_UserAlerts_WeightTracking
    where userid = @Userid and UpdatedDate between @EndDate and @StartDate
    ;with aweek(day,weight) as
    ( select @StartDate as day,’0′ as weight
    union all
    select day – 1,’0′ as weight from aweek
    where day > @EndDate )

    select aweek.day as WeightDate ,case when #tempWeight.weight IS NULL then ‘0’ else #tempWeight.weight end as weight
    from aweek left outer join #tempWeight on #tempWeight.Updateddate = aweek.day order by WeightDate asc
    Drop table #tempWeight

    END

    exec dev_GetAllWeekCharts_M 4,’2012/04/02′

    But is show error message as
    (5 row(s) affected)
    Msg 530, Level 16, State 1, Procedure dev_EvolveSystems_GetAllWeekCharts_M, Line 14
    The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

    thanks.
    RAM

  170. Please tell me how get MIN Amount Date between 2011-12-31 and 2012-03-31 below table

    Date Amount
    2011-11-01 4,500.00
    2012-02-15 5,300.00
    2012-03-16 6,000.00

  171. hi can u help me plz i want to get all data from current date to last date till which schedule hv been created from schedule tabel that contain date column

  172. dear frindes please, who can help me i want to get only year from the database and then subtract a value from it. plz looking for your kind help.

  173. @Mirwais,

    In order to just extract the year, use command

    select DATEPART(year,getdate())

    Example of how to substract value from it:

    select 2014 – DATEPART(year,getdate())

  174. hello,
    I have tried
    DECLARE @X int
    SET @X= DAY(getdate())
    and the same things for year and month

    and I increase DAY and I want to make that values in datetime (yyyy-mm-dd)
    how can I do that

  175. actually my problem is that I want to get date from my existing table and increase day then format new datetime

  176. Hi sir i want to fetch date from system but the condition is that
    I want to fetch yesterdays and current date before 5:30 pm and the date must be not in 2nd and Third saturday and other sundays

  177. This syntax is used to display only date (2011-11-01 00:00:00 )
    eg syntax:

    convert(varchar(10),Date_Da,101) as Date_Da

    This Date_Da that field is our table date field

  178. sir;
    i am facing a problem .i want to find current date and time of indian format .
    when we host the application then time is not in good format as like pm to change in am
    sir, plz help me

  179. I’m wanting to find records that are older than 85 days from today. When I put the following code: Category_3 < dateadd(day, -85, getdate())
    Once excuted I then get the following error:
    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. Error Code: 242

    Any ideas on how to fix this?

    • I fixed my own issue. Should have had:
      Category_3 < Convert(Varchar(10),dateadd(day, -85, getdate()))

      However now would love to sort these results in DESC order. ORDER BY Category_3 DESC doesn't work (I assume because of the Varchar). Any ideas?

  180. I want month and year-wise Reports so how to pass Month and Year Parameters in Same Stored Procedure..Please Help Me as soon as

  181. Pingback: SQL SERVER – Get Date and Time From Current DateTime – SQL in Sixty Seconds #025 – Video « SQL Server Journey with SQL Authority

  182. Thanks yet again… PinalDave, I have used your teachings many times and well, never offered a thank you. So… Thank you. Anytime I do a query for SQL help, I check your links first! You have been most helpful so many times – so thanks for this one, and all of them!

    • This is formation issues that should be handled by your application. However here is the answer for sql

      select convert(char(8),getdate(),112)+replace(convert(char(10),getdate(),108),':’,”)

  183. Hi Sir,
    I m facing a problem that is:
    I have two columns as CreateDate and ExpiryDate in ASPNETDB.MDF file, I want to put days between two date in DaysLeft Column.
    I m using Visual Web developer 2005 express Edition. I m using the function

    Datediff(Day,convert(datetime,CreateDate),Convert(Datetime,ExpiryDate)) in computed column specification

    but failed. Please reply

  184. I am creating an SSIS to export queried data to excel. I want to pull records from yesterdays date. Seems like the following should work but I get no return
    SELECT * FROM TableName
    WHERE dateTimeMix = dateadd(dd, -1, getdate())

  185. Hi Sir, can you please assist me I am trying to insert the current time in a timestamp field.
    like

    create table x ( a timestamp not null)

    INSERT INTO x
    VALUES CAST(current_timestamp AS TIMESTAMP)

    This alone works
    SELECT CAST(current_timestamp AS TIMESTAMP) but if I want to insert it into a table it does not work . I am aware that it converts it to binary on the table when it was successful

  186. I am trying to convert to Month/Day/Year formant,
    from Day/Month/Year format.

    Will this statement work, or should it be 101 rather than 103 ???

    SELECT WorkOrderID, Convert(varchar(12),ModifiedDate,103) FROM Production.Workorder

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

  188. Hi All,

    I wanted a query which will get records between current date and 20 days before also if i can group them to each day. Can any body help?

    Regards,
    Sachin

  189. Hello, It is a very helpful page. Can anyone tell me how to retrieve only TIME part and DATE/TIME of Database of 2011 from database MS SQL Server 2000. If there is no field of TIME in the table, even then if someone is able to retrieve Time of data storing?

  190. I want to
    Create a table having fields are as follows
    Create table emp
    (
    Empname varchar(50),
    joining SystemDate,
    sal int
    )
    Plz help me whats the actual Query to get systemdate in this table

  191. Hello ,
    i want to search txt file like as 13.03.2014_15.59. txt (current date and time ).
    **********************************
    bulk insert TmpStList
    from ‘C:\PATH\ currentdate_time.txt ‘
    with (fieldterminator = ‘,’, rowterminator = ‘\n’)
    *************************************************** is what i want to do.

  192. why i get this error:
    “The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
    The statement has been terminated.”
    i want to insert datetime.now into sql where the type is also datetime
    but somtimes its not working.. i get above error..
    how can i insert datetime.now as datetime in sql… i want to check the datetime stored in sql with current datetime (it must be of the form dd/mm/yyyy hh:mm:ss am/pm or mm/dd/yyyy hh:mm:ss am/pm)
    pls help me with the above problem…

  193. The time is not server time but the time of the machine running the SQL (e.g. if you run the query on a SQL Management studio in one country and it connects to a server on another country – the time will be of the local machine – not the remote)

  194. Hi! Congratulations for your blog is awsome!
    One question:
    if i want to setup the date of the database with a configuration file… how can i achieve this?
    So for example if the configuration file says it is 12/12/12 getdate() retrieves that date…
    thank youuu

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