Date and DateTime format in SQL Select, Insert and Update

Posted on 2006-05-01
Last Modified: 2010-05-18
I'm sure this will be elementary for most of you using SQL.

I'm somewhat at a standstill as to how to format several datet and time fields in my app as associated with SQL statements.  I am in the process
of creating some stored procedures that have both date and time fields in them which may contain data and also may be null.  Several fields that are
blank or null will be included in an ascending display in a grid.

Needless to say I don't want '(null)' to display in my grid, yet when I use the following CONVERT statement I think I lose my ability to ORDER the grid
in date order because the SQL statement is looking at reported_date below as string.

"ISNULL(CONVERT(VARCHAR(10), violation.reported_date, 1),'') AS reported_date, "

If I use only "ISNULL(violation.reported_date,'') AS reported_date"         IT DISPLAYS 1/1/1900

The SQL Server 2000 Bible I am using must be for advanced SQL developers.  It doesn't cover basics like the one above.

Could someone please help me with the following:

1)  Modifying my SQL statement as discussed above.

2)  The best field type to save a group of time values in a table to include in a combo box.

3)  Using the combo box in #2...the best way to format in the combo box and then store a 'time' value in a separate table.  An example would be the Calendar/Appointment Start/End Time fields in Outlook.

Thanks for the help.

Question by:TSFLLC
    LVL 5

    Expert Comment

    Do you want to alter the data in the SQL statement or in your VB application?  VB has a great function called Format() so you type in Format(Now(), "mm/dd/yy") will return 05/01/06.  Or you can change it in the SQL statement but that would be a better question for the SQL Server boards.

    Author Comment

    The date and time fields would be saved into a datatable and then use parameters/SQL stored procedure for updating.

    I have used Format() in MS Access for years in terms of formatting text boxes, SQL statements used for populating grids, and saving into tables.  SQL 2000 apparently doesn't recognize Format().

    My problem #1 may be better served in the SQL Server board.  I just included it in here because I have seen a multitude of problems resolved that included SQL as part of them.  My SQL problem really only deals with one date field being displayed in a VB datagrid.

    An Access grid doesn't display (null) in a date field.  It just doesn't display anything.
    LVL 5

    Expert Comment

    I used to work in Access alot before I moved to SQL Server.  I feel your pain :)

    What I did was write a custom UDF that takes a date and returns a string.  I called it FormatDate() and I never know the difference :)

    you can go down that road and try it.  you may be able to alter the dataset before it gets binded to the datagrid in VB.NET with the VB format function but I've haven't done much work with that.
    LVL 5

    Accepted Solution

    you can alter the column of the datagrid.  set the NullText property = "" and it'll show blanks instead of <NULL>.

    Author Comment


    I'm a little embarassed now.  Didn't know that using .NullText = "" was going to be that easy.

    That completely resolved my #1 problem.  I dont' have to do any type of conversion or adjustment to a date field in a SQL statement with this property being available.  I'm definitely going to re-review all of the properties for a datagrid.

    In the meantime, I decided to create my 'time' field as a datetime and enter just valid time format and will work on this by myself for a time.


    LVL 5

    Expert Comment

    good luck with your project!

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
    It’s quite interesting for me as I worked with Excel using for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    This video discusses moving either the default database or any database to a new volume.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now