PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
Custom date format-VBForums
Results 1 to 13 of 13

Thread: Custom date format

  1. #1

    Thread Starter
    Member
    Join Date
    Sep 2017
    Location
    UK, France, Belgium, Ireland
    Posts
    34

    Custom date format

    I'm retrieving a date from a date column in the database, and want to apply my own, custom formats, like "dddd d MMMM yyyy", or just "ddd", etc. to display in a Label or a TextBox.

    I've found the FormatDateTime function (from here), which I can use as follows:

    txtStartDate.Text = Strings.FormatDateTime(reader.Item("colStartDate").ToString, DateFormat.LongDate)

    ...but I don't want the LongDate format that gets output.

    I'm guessing this might be a very common question, so sorry in advance, but I cannot find the answer. I've found lots of advice on how to convert a VB date to a custom string, but I want to convert a database date to a custom string.

  2. #2
    Fanatic Member PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Newport, UK
    Posts
    846

    Re: Custom date format

    http://blog.stevex.net/string-formatting-in-csharp/ might be worth a look, although the article mentions C# the formatting codes work the same with VB.Net and the ToString method.

  3. #3
    You don't want to know.
    Join Date
    Aug 2010
    Posts
    4,580

    Re: Custom date format

    This documentation page tells you everything you need to know about a custom date/time format in .NET. Read it through, and get an idea of what's there, you'll consult it hundreds of times. (I need to use it several times a month!) Bookmark it!

    Composite Formatting is a good general topic that covers it all. Generally, if you want to format a DateTime, you'd either use String.Format() or pass the format string to DateTime.ToString(). This article tells you all you need to know in conjunction with the other one.

    The date OUGHT TO come out of the database as a .NET DateTime value. It's generally not a good idea to store them as Strings in the database.
    This answer is wrong. You should be using TableAdapter and Dictionaries instead.

  4. #4

    Thread Starter
    Member
    Join Date
    Sep 2017
    Location
    UK, France, Belgium, Ireland
    Posts
    34

    Re: Custom date format

    Quote Originally Posted by PlausiblyDamp View Post
    http://blog.stevex.net/string-formatting-in-csharp/ might be worth a look, although the article mentions C# the formatting codes work the same with VB.Net and the ToString method.
    I know about date formats in VB.Net so I don't want to learn about them in C#. But thanks anyway.

    Quote Originally Posted by Sitten Spynne View Post
    This documentation page tells you everything you need to know about a custom date/time format in .NET. Read it through, and get an idea of what's there, you'll consult it hundreds of times. (I need to use it several times a month!) Bookmark it!

    Composite Formatting is a good general topic that covers it all. Generally, if you want to format a DateTime, you'd either use String.Format() or pass the format string to DateTime.ToString(). This article tells you all you need to know in conjunction with the other one.

    The date OUGHT TO come out of the database as a .NET DateTime value. It's generally not a good idea to store them as Strings in the database.
    I came across one if not both of those in my searching. They don't tell me what I'm supposed to do to convert a database date into a custom-formatted VB string

    To clarify:
    1. I have a database column "colStartDate" (data type: "date").
    2. In VB I use reader.Item("colStartDate") in getting the date value from the database.
    3. I want to display the date on a VB.Net Windows form in my own, special format.
    4. I was hoping for some simple function like MySpecialFormatDatabaseDateAsStringFunction, using it in some way like this:
    Code:
    txtStartDate.Text = 
    MySpecialFormatDatabaseDateAsStringFunction(reader.Item("colStartDate"), "dddd d MMMM yyyy")

  5. #5
    You don't want to know.
    Join Date
    Aug 2010
    Posts
    4,580

    Re: Custom date format

    I don't know what "a database date" means.

    If one of the DataReader types is reading a column that has a specific date column type, it is very likely to be returning a .NET DateTime. So I would expect to be able to do something like this:
    Code:
    Dim startDate = CType(reader("colStartDate"), DateTime)
    txtStartDate.Text = startDate.ToString("dddd d MMMM yyyy")
    It may not be the case that the column value is a DateTime, I can't tell because I don't know enough details. For example, SQLite doesn't really have an official "date" type, it just has functions that kind-of-sort-of support dates, so what it means to access a column that holds "a date" can be application-specific. There are lots of different database engines, and some of them have multiple .NET libraries for accessing them, so we'd need to know some specifics to tell you what to do if something like the above isn't working.
    This answer is wrong. You should be using TableAdapter and Dictionaries instead.

  6. #6

    Thread Starter
    Member
    Join Date
    Sep 2017
    Location
    UK, France, Belgium, Ireland
    Posts
    34

    Re: Custom date format

    Quote Originally Posted by Sitten Spynne View Post
    I don't know what "a database date" means.

    If one of the DataReader types is reading a column that has a specific date column type, it is very likely to be returning a .NET DateTime. So I would expect to be able to do something like this:
    Code:
    Dim startDate = CType(reader("colStartDate"), DateTime)
    txtStartDate.Text = startDate.ToString("dddd d MMMM yyyy")
    It may not be the case that the column value is a DateTime, I can't tell because I don't know enough details. For example, SQLite doesn't really have an official "date" type, it just has functions that kind-of-sort-of support dates, so what it means to access a column that holds "a date" can be application-specific. There are lots of different database engines, and some of them have multiple .NET libraries for accessing them, so we'd need to know some specifics to tell you what to do if something like the above isn't working.
    Thought all DBMSs had a DateTime data type or similar (at least, I know SQL Server, Oracle and Access do). Anyway, many thanks for that! It works. I hadn't thought of converting the data type.

    Edit: I can't rate your post in thanks for the answer -- says I need to spread some Reputation around first --
    seems I've thanked you last time or something!
    Last edited by ktrammen; Dec 3rd, 2017 at 03:58 PM.

  7. #7
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    99,764

    Re: Custom date format

    Quote Originally Posted by ktrammen View Post
    I hadn't thought of converting the data type.
    Yes you had, because you were already doing so. If you have a .NET DateTime value, which you do because that's what ADO.NET returns when retrieving native date values from a database, then the only possible way to enforce a format on it is to convert it to a String and that's exactly what you were doing from the start. In the code you provided in post #1 you call ToString and FormatDateTime, both of which convert to a String. The only difference in Sitten Spynne's code is that he cast the Object reference returned by the Item property of the data reader as the actual type of the value, i.e. DateTime, first. By doing so, you then get access to the ToString method of the DateTime type, which accepts a format specifier, rather than just of the Object type, which doesn't. The value always was a DateTime though. The data reader just exposes it via an Object property because that same property has to be able to expose data of other types too. You should be casting data coming out of a data reader the majority of the time, e.g. if you were to set the Value of a DateTimePicker you would need to perform the same cast.

    By the way, this:
    Code:
    CType(reader("colStartDate"), DateTime)
    can be shortened to this:
    Code:
    CDate(reader("colStartDate"))
    All the intrinsic types in VB (String, Integer, Bolean, etc) have a similar abbreviation for CType.
    Last edited by jmcilhinney; Dec 3rd, 2017 at 05:54 PM.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  8. #8
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,767

    Re: Custom date format

    Doesn't the DataReader also have .GetString, and a .GetInteger, and a .GetDate, as well as a number of .Get methods that will return the value in the correct type?

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  9. #9
    You don't want to know.
    Join Date
    Aug 2010
    Posts
    4,580

    Re: Custom date format

    2 replies:

    @JMC: I'd always assumed CDate() converted to Date, which I also assumed was some kind of wonky VB6 compatibility type, but it turns out upon research in VB, "Date" is an alias for System.DateTime. I don't really have an argument for the "right" one between CDate(), CType(), or DirectCast(), but I tend to reach for CType() out of habit!

    @techgnome: Those methods do exist, but they want a column index, not a name. I always like to use the column names, when I can, though if you don't use SELECT * then there's a fighting chance you can predict your column order. The IDataReader interface gives you enough information you can actually deduce the right index, but it's overhead unless you build a lookup table (which is probably what the String indexer does.)
    Last edited by Sitten Spynne; Dec 4th, 2017 at 05:13 PM.
    This answer is wrong. You should be using TableAdapter and Dictionaries instead.

  10. #10
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    99,764

    Re: Custom date format

    Quote Originally Posted by Sitten Spynne View Post
    I'd always assumed CDate() converted to Date, which I also assumed was some kind of wonky VB6 compatibility type, but it turns out upon research in VB, "Date" is an alias for System.DateTime. I don't really have an argument for the "right" one between CDate(), CType(), or DirectCast(), but I tend to reach for CType() out of habit!
    Technically, DirectCast is the correct one to use if you're performing a cast and CType is the correct one to use if you are or may be performing a conversion. CDate, CStr, etc, produce the same IL as CType and produce shorter - I think clearer - code and so are better alternatives to CType for that reason. Given the negligible effective difference, I always tend to use CDate, CStr, etc, even when casting for that conciseness.
    Quote Originally Posted by Sitten Spynne View Post
    Those methods do exist, but they want a column index, not a name. I always like to use the column names, when I can, though if you don't use SELECT * then there's a fighting chance you can predict your column order. The IDataReader interface gives you enough information you can actually deduce the right index, but it's overhead unless you build a lookup table (which is probably what the String indexer does.)
    Instead of this:
    vb.net Code:
    1. Dim str = CStr(myDataReader("ColumnName"))
    you can do this:
    vb.net Code:
    1. Dim str = myDataReader.GetString(myDataReader.GetOrdinal("ColumnName"))
    That's a bit more verbose but it's for things like this that everyone should build up their own library of extension methods. If you put something like this into a DLL:
    vb.net Code:
    1. Imports System.Data.Common
    2. Imports System.Runtime.CompilerServices
    3.  
    4. Public Module DbDataReaderExtension
    5.  
    6.     <Extension>
    7.     Public Function GetDateTime(source As DbDataReader, columnName As String) As Date
    8.         Return source.GetDateTime(source.GetOrdinal(columnName))
    9.     End Function
    10.  
    11.     <Extension>
    12.     Public Function GetString(source As DbDataReader, columnName As String) As String
    13.         Return source.GetString(source.GetOrdinal(columnName))
    14.     End Function
    15.  
    16. End Module
    then you can use it like this any time you want:
    vb.net Code:
    1. Dim str = myDataReader.GetString("ColumnName")
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  11. #11
    You don't want to know.
    Join Date
    Aug 2010
    Posts
    4,580

    Re: Custom date format

    Agreed, with minor stipulation: "I find very few people on the forums listen to my answers when I get excited and suggest 'helper methods', let alone extension methods."
    This answer is wrong. You should be using TableAdapter and Dictionaries instead.

  12. #12
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    99,764

    Re: Custom date format

    Quote Originally Posted by Sitten Spynne View Post
    Agreed, with minor stipulation: "I find very few people on the forums listen to my answers when I get excited and suggest 'helper methods', let alone extension methods."
    That may be a throw-away line but I'll respond anyway. That doesn't seem to be a reason not to suggest it. If someone refuses to see the value in it then it's on them that they will never be a decent developer but there are those who will see the value, either now or later, and benefit from it as a result. They may even become excited when they realise what things like this can do for them. They're the people that I'm genuinely keen to reach.
    Last edited by jmcilhinney; Dec 4th, 2017 at 06:12 PM.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  13. #13
    You don't want to know.
    Join Date
    Aug 2010
    Posts
    4,580

    Re: Custom date format

    I go back and forth. I think I can make a strong argument for "answer with the least complexity" and "answer with a higher degree of professionalism" and, given infinite time, I'd like to choose "post both" when I can. A lot of times the reason a novice doesn't write code at a higher level is they don't understand how to go about writing it that way. We don't tend to make it known, "I wrote the 100-line version first, then started breaking pieces out, and after about 5 years of doing that I started understanding where to make the pieces before I finished the 100-line version."
    This answer is wrong. You should be using TableAdapter and Dictionaries instead.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width