locked
Use date as a Parameter RRS feed

  • Question

  • User882635185 posted

    Hi,

    I am trying to but unsuccessfully assign todays server date (English Format if possible) to a variable and then assign that to a parameter to use in my insert statement but am not sure how to do it.

    Below is how I am assigning variables to parameters at the moment, but I am not sure how I do it just for a standard variable like if I assigned the date to one?

     

    cmdInsert.Parameters.Add("@P_ID", SqlDbType.Int).Value = DropAuthors.SelectedValue
     
    Thanks for your time. 
    Friday, February 16, 2007 4:47 PM

Answers

  • User1335583151 posted

    Hello my friend,

    Try the following: -

    cmdInsert.Parameters.Add("@MyDateParam", SqlDbType.DateTime, 8).Value = Date.Today

     

    Kind regards

    Scotty

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 16, 2007 4:56 PM
  • User-821857111 posted

    In your SQL, you would have something like this:

    INSERT INTO Table (field1, field2, datetimefield) VALUES (@Parameter1, @Parameter2, GetDate()) 

    so the  value for the datetime field is automatically provided by SQL.  How this appears in the database table will invariably depend on the regional settings etc.  Try it then have a look at what happens.  Also, retrieving it and displaying it on the page is regional settings-dependent.  Again, try it out,   You may get what you are hoping for without any messing around at all.

    Also, you may get some help form this: Demystifying the SQL Server DATETIME Datatype

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, February 18, 2007 12:05 PM

All replies

  • User-821857111 posted

    Forget messing around with parameters for this.  Use the SQL GetDate() function instead. That will pop the current date in for you.


    Friday, February 16, 2007 4:56 PM
  • User1335583151 posted

    Hello my friend,

    Try the following: -

    cmdInsert.Parameters.Add("@MyDateParam", SqlDbType.DateTime, 8).Value = Date.Today

     

    Kind regards

    Scotty

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 16, 2007 4:56 PM
  • User882635185 posted

    Sorry to bring this up again, but is there anyway I can format the date to have dd/mm/yyyy - instead of the using the way above which inserts 'Feb 17 2007 12:00AM'

    Thanks very much. 

    Saturday, February 17, 2007 8:16 AM
  • User882635185 posted

    Anyone any ideas with this?

    Thanks 

    Saturday, February 17, 2007 3:35 PM
  • User-821857111 posted

    If you take my advice and use GetDate() in your SQL, you won't have any issues formatting the date.  The database will do it for you.  Ideally, you should store dates in the database in the way it wants them, then do any formatting when you retrieve them into your page.  Believe me, doing it this way will save you a whole load of grief in the future.

    Blimey, the number of hours I've wasted in the past because Americans can't write dates the right way round.... [;)] 

    Sunday, February 18, 2007 3:57 AM
  • User882635185 posted

    Thanks for your reply's Mike, I have had problems with dates before as well, but have never really got to the bottom of it and always seem to give up, so I need to get it sorted really.

    I have used the 'cmdInsert.Parameters.Add("@MyDateParam", SqlDbType.DateTime, 8).Value = Date.Today' string and this inputs the whole date, but please can you explain how I would use the GetDate() function you are talking about?

    Also is retrieving the dates the proper (English) was going to be hard?

    Thanks 

    Sunday, February 18, 2007 6:29 AM
  • User-821857111 posted

    In your SQL, you would have something like this:

    INSERT INTO Table (field1, field2, datetimefield) VALUES (@Parameter1, @Parameter2, GetDate()) 

    so the  value for the datetime field is automatically provided by SQL.  How this appears in the database table will invariably depend on the regional settings etc.  Try it then have a look at what happens.  Also, retrieving it and displaying it on the page is regional settings-dependent.  Again, try it out,   You may get what you are hoping for without any messing around at all.

    Also, you may get some help form this: Demystifying the SQL Server DATETIME Datatype

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, February 18, 2007 12:05 PM