Post reply

SQL Server DataTime Format Question

  • November 10, 2004 at 11:12 am

    #162701

    Hi

    I am kind of new to sql server stored proc coding.  I have a table which has  a column with a datatype (DATETIME).  and I need to insert a date in MM/YYYY format.  Almost all the date format options I have seen so far include the the date.  I just need to store the Month and the year, that the user enters.  Is there any way to do this?  The front end application is aspx with vb.net.  Any help or suggestions would be greatly appreciated.  Thanks in advance.

    Dinesh

  • November 10, 2004 at 11:25 am

    #529670

    If you want to store the date as MM/YYYY, then you will need to store it as a varchar.

    If you need to parse out the MM/YYYY in SQL:

    cast(datepart(mm,getdate())as varchar)+'/'+cast(datepart(yy,getdate())as varchar)

  • November 10, 2004 at 12:14 pm

    #529682

    Why not store YYYYMM01 -- First of the month -- and then parse the month and year for display purposes on the client?

    --
    Adam Machanic
    whoisactive

  • November 14, 2004 at 8:13 pm

    #530063

    Adam is correct... basically, there is no way to store just the Month and Year in a datetime field.  In fact, you cannot store a "formatted" date in a datetime field (too long to explain why here).  You must also store a day and the first day of the month is as good as any and better than most because every month has a "first day". 

    You can display the date in the desired format as Osoba suggested or by using the following formula in your SELECT statement:

    RIGHT(CONVERT(VARCHAR(10),yourdatecolumn,103),7)

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • December 14, 2004 at 2:15 am

    #533706

    If you have a database with all kinds of dates and want to set it to dd:mm:yyyy:23:59:59.
    Thus all the datetimes keep their dates and get the same time: 23:59:29
    Is there and easier way than using three functions dateadd,floor and cast?
     
     
    select pro_created, dateadd(ss,86399,floor( CAST(pro_created AS float)))

    from pro

    where pro_created is not null

     

  • December 14, 2004 at 7:09 am

    #533752

    Why would you use 23:59:59 rather than 00:00:00 ? The latter, IMO, is more intuitive...

    --
    Adam Machanic
    whoisactive

  • December 14, 2004 at 8:56 am

    #533779

    The question was to change it to 23:59:59. I did not ask why.

  • December 14, 2004 at 9:05 am

    #533781

    Here's another way, slightly more intuitive IMO (as it does not rely on conversion to FLOAT/knowledge of SQL Server's internal date format):

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

    --
    Adam Machanic
    whoisactive

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply