sql - Extract Month-Year from a unix timestamp

mytable.timecreated is a BIGINT column on a SQL Server 2008 R2 database and it contains a unix timestamp.

This query:

SELECT DATEADD(s,mdl_user_enrolments.timestart,'19700101 02:00') as TheDate
FROM mytable

gives me the date in this format:

2015-05-30 20:30:05.000

How do I retrieve just the Month and Year from that timestamp, like this:

May 2015

The following SQL seems to do the job, but is there a simpler way to do it?

SELECT 
    CAST(
        CASE 
            WHEN month(dateadd(s,timecreated,'19700101 02:00')) = 1 Then 'January'
            WHEN month(dateadd(s,timecreated,'19700101 02:00')) = 2 Then 'February'
            WHEN month(dateadd(s,timecreated,'19700101 02:00')) = 3 Then 'March'
            WHEN month(dateadd(s,timecreated,'19700101 02:00')) = 4 Then 'April'
            WHEN month(dateadd(s,timecreated,'19700101 02:00')) = 5 Then 'May'
            WHEN month(dateadd(s,timecreated,'19700101 02:00')) = 6 Then 'June'
            WHEN month(dateadd(s,timecreated,'19700101 02:00')) = 7 Then 'July'
            WHEN month(dateadd(s,timecreated,'19700101 02:00')) = 8 Then 'August'
            WHEN month(dateadd(s,timecreated,'19700101 02:00')) = 9 Then 'September'
            WHEN month(dateadd(s,timecreated,'19700101 02:00')) = 10 Then 'October'
            WHEN month(dateadd(s,timecreated,'19700101 02:00')) = 11 Then 'November'
            WHEN month(dateadd(s,timecreated,'19700101 02:00')) = 12 Then 'December'
        END AS NVARCHAR(10))
    + ' ' +
    CAST(YEAR(dateadd(s,timecreated,'19700101 02:00')) AS NVARCHAR(5)) AS MyDate
FROM mytable

3 Answers

  1. Kenny- Reply

    2019-11-14

    How about using datename()?

    select (datename(month, dateadd(second, timecreated, '19700101 02:00')) + 
            datename(year, dateadd(second, timecreated, '19700101 02:00'))
           )
    

    I'm leaving 02:00 in this because that is how you phrased your question. THe correct conversion, though, would use 00:00, unless you are trying to correct for a time zone.

    Also note that the values returned by datename() depend on your internationalization settings, so they are not necessarily in English.

  2. Kevin- Reply

    2019-11-14

    There are some built in CONVERT functions you can use. I don't know if there is one for the specific format that you want, but 106 is close enough that can be combined with the RIGHT function to get what you want, like this:

    SELECT
    RIGHT(CONVERT(VARCHAR(11),mdl_user_enrolments.timestart,106), 8) as MyDate
    FROM MyTable
    
  3. Lance- Reply

    2019-11-14

    What language are you using? I would just put a UNIX timestamp in the database and then, using PHP, I would do

    <?php date("M Y", strtotime($your_unix_timestamp));
    ?> 
    

    but then you have to get the data from SQL into PHP so if you're going to be using PHP anyway, it might be easier

Leave a Reply

Your email address will not be published. Required fields are marked *

You can use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>