I want to get the day of a date in a form

[JohnDW]

Hello,

I have the following sql query to fill a datagrid

[FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]Cmd.CommandText = [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#a31515][FONT=Consolas][SIZE=2][COLOR=#a31515][FONT=Consolas][SIZE=2][COLOR=#a31515]"Select ViewTotaal.Expr1 As [Totale Verkoop], ViewTotaal.Datum, [U]Datum(day)[/U] As Day From ViewTotaal "[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT]

The underlined column gives me a problem. In that column I want the day of Datum. Datum = "yyyy/mm/dd" .Is it possible that for each datum (day,month,year of the week) the query returns the day (like monday, tuesday, etc..)


Grtz, John

 

[jmcilhinney]

I doubt that the database itself would do that but VB can do it for you easily. Just change your query to get the whole date and then, in your application, specify the appropriate format string when you display the date(s). The format string for the full name of the day is "dddd", e.g.

MessageBox.Show("Today is " & Date.Today.ToString("dddd"))

 

[JohnDW]

DataGridView1.Columns(2).DefaultCellStyle.Format = "dddd"

I'll try the code above, but that gives me the same date ("dd/mm/yyyy") while
I want "dddd".

Suggestions?

Thanks,

John

 

[jmcilhinney]

What is the data type of Datum in the database?

 

[JohnDW]

the problem lies there. I hit my data in a text field.
I tried again to convert:
CAST (CONVERT (char (10); dbrders.Orderdatum, 111) AS datetime)
and this worked.

thanks a lot for the support,

John

 

[jmcilhinney]

the problem lies there. I hit my data in a text field.

Why do such a thing? Do you store numbers as text or as numbers? I'm guessing the latter, so why store dates as text when pretty much every database has a data type dedicated to dates and/or times and SQL Server has several. If possible, change your design and store date values in a proper column of an appropriate data type.

 

[JohnDW]

In the db it's stored as datetime. But I use the convert above in a query and I want to see the day of the field 'Orderdatum' in the DGV.
Thanks for your support.

John

 

[jmcilhinney]

As I have already stated, just retrieve the whole date from the database and then format the value(s) locally using the appropriate format string.