Date and DateTime format in SQL Select, Insert and Update

Posted on 2006-05-01
Medium Priority
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
  • 4
  • 2

Expert Comment

ID: 16579193
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

ID: 16579412
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.

Expert Comment

ID: 16579489
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.

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


Accepted Solution

MageDribble earned 500 total points
ID: 16579531
you can alter the column of the datagrid.  set the NullText property = "" and it'll show blanks instead of <NULL>.

Author Comment

ID: 16579916

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.



Expert Comment

ID: 16579938
good luck with your project!

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

864 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