Important date and time function tips

by Ravi Tuvar 5:11 AM 0 comments

Hi guys here are some of the basic date time tips that we usually use in sql..

1) SELECT 

    DAY(GETDATE()) as  Day, 

    MONTH(GETDATE()) as Month, 

    YEAR(GETDATE()) as Year,

    DATEPART(DAY, GETDATE()) as DatePartDay, 

    DATEPART(MONTH, GETDATE())as DatePartMonth,

    DATEPART(YEAR, GETDATE())as DatePartYear

Result:

Day            Month       Year        DatePartDay DatePartMonth DatePartYear
----------- ----------- ----------- ----------- -------------       ------------
13             2                2012        13               2                         2012



2) SELECT
    GETDATE() AS local_date,
    GETUTCDATE() AS UTC_date

Result:

local_date                                UTC_date

-----------------------            -----------------------

2012-02-13 18:33:07.593      2012-02-13 13:03:07.590

3) SELECT DATEADD(MONTH, 6, GETDATE())AS '6_months_from_now'
Result:

6_months_from_now
-----------------------
2012-08-13 18:34:43.403

4) declare @datevar datetime
    select @datevar = getdate()
    select @datevar as DateVariable

Result:

DateVariable
-----------------------
2012-02-13 18:36:21.030

5)

declare @datevar datetime
select @datevar = getdate()

/*getting current datetime*/
select getdate() [Current Datetime]

/*getting date 7 days from current datetime*/
select dateadd(dd, 7, @datevar) [Date 7 days from now]

/*getting no of days passed since 01-01-2004*/
select datediff(dd,'20040101',@datevar) [No of days since 01-01-2004]

/*getting month name*/
select datename(mm, @datevar) [Month Name]

/*getting week from date*/
select datepart(wk, @datevar ) [Week No]

/*getting day part of date*/
select day (@datevar) [Day]

/*getting month part of date*/
select month(@datevar) [Month]

/*getting year part of date*/
select year(@datevar) [Year]

/* Getting the Day Name like monday tuesday... */
SELECT DATENAME(dw, GETDATE())
-- or
/* 0-monday,1-tuesday,2-wednesday ....7-monday,8-tuesday... */
SELECT DATENAME(dw, 0)