CODEWITHSUNDEEP
sql-serverdatetimevb6
barry17
barry17

Reputation: 153

formatting date on insert to sql?

I have a VB6 application where I insert a set of dates into a SQL-SERVER. Each time I insert a value, it gets inserted as 1978-12-12 00:00:00.000. Is it possible to specify in the INSERT statement, how you want the date to be formatted? VB6 does not seem to recognize CONVERT. I did previously CONVERT date when I loaded it into a MSHFlexGrid like this:

Convert(varchar,tblClient.DOB, 101)

But I did this in a select statement. Will SQL let me insert a value in a format MM/DD/YYYY as I need it later in that format. The reason why I need the formatting is because I connected all my tables in SQL-SERVER2008 to Access for report generating purposes. So I need it formatted correctly in SQL-SERVER2008 as it dynamically connects to Access.

Upvotes: 0

Views: 2810

Answers (3)

George Mastros
George Mastros

Reputation: 24498

Ideally, the data type of the column in the database is set to Date or DateTime. Basically, if you want to store a date, then use a date date type.

That being said, in VB6 you usually have to (at least temporarily) store the date as a string so there is almost always a string to date conversion that happens somewhere.

Will SQL let me insert a value in a format MM/DD/YYYY

Yes. But you should not do this. Instead, you should insert the date with the format "YYYYMMDD". Notice that there are no delimiters. The problem with mm/dd/yyyy is that it could accidentally be interpreted as the wrong date. For example, 1/2/2015 would be interpreted as Feb 1, 2015 if you lived in England, or Jan 2, 2015 if you live in the US. However, SQL Server will always interpret 20150102 and Jan 2, 2015.

Once you have the data stored the way you want in the database (as an actual date data type), you should actually return it as a date to your front end (either Access or VB6). In the front end, you should use the format command to display the date. The format command will use the regional settings of the computer to display dates the way the user wants to see it.

Ex:

txtDateOfBirth.Text = Format(rs.Fields.Item("DOB").value, "Short Date")

Doing things this way... you should never have problems with dates.

Upvotes: 2

Pieter B
Pieter B

Reputation: 1967

The best way is not to store formatted dates in your database server.

One way you can get what you want is by using a view where you format your data and use that as input for your report:

CREATE VIEW myreport
SELECT replace(convert(NVARCHAR, mydate, 106), ' ', '/') from mytable

But I would recommend formatting dates on the application level.

Upvotes: 1

David Rushton
David Rushton

Reputation: 5030

You can use VB6s format function prep the date before inserting it into SQL. Here's an example (tested in VBA).

Format(Now(), "YYYY-MM-DD")

Upvotes: 0

Related Questions