Date Convertion from yyyy-mm-dd To dd/mm/yyyy

[bashu]

Hi to ALL
Here I am using .net 2.0 and MS SQL 2K. In our database table DateTime saved as in the format of 2007-01-31 8:33:19.000(yyyy-mm-dd) to access to this records by searching based on Date, when we are searching based on Date that Date format would be (dd/mm/yyyy). How we can convert the date format.
Data Base Date Format: 2007-01-31 (yyyy-mm-dd)
Search Criteria Date Format: 31/01/2007 (dd/mm/yyyy)

I have written following Code:
----------------------------------------------------------------------------
Select distinct tbl_adminuser.adminUserName,tbl_adminCategory.Name , COUNT(dbo.tbl_outbox.msgUserID) As

TotalCount

FROM dbo.tbl_adminuser,dbo.tbl_AdminCategory, dbo.tbl_outbox

where tbl_adminuser.adminUserID = dbo.tbl_AdminCategory.CatID and tbl_AdminCategory.CatID =

dbo.tbl_outbox.msgUserID

and tbl_outbox.msgUserID <> 0 and Convert(varchar,tbl_outbox.msgDate,103)>=@fromdate and
convert(varchar,tbl_outbox.msgDate,103)<=@todate
group by tbl_adminuser.adminUserName, dbo.tbl_AdminCategory.Name
--------------------------------------------------------------------------------------


Thanks in Advance
Bashu

[brju]

I am only familiar with SQL string constructs within the Access VBA environment, but there have been instances where we wanted to ensure a specific date format - this is the string we used:

strSql = strSql & " WHERE OrderDate = #" & Format(OrderDate, "DD MMM YYYY") & "# "

Hope this helps!

[Allan Murphy]

I am not familar with .NET or SQl but I can offer a solution that works in Access using VBA.

Access has a function called MID that is used to return a Variant (String) containing a specified number of characters from a string.
Mid(string, start[, length])
string is the string from which the characters are to be returned, start is the starting position in the string and length is how many charcters to be returned.


In the coding below using
date_input as 2007-01-31 8:33:19.000
date_out becomes 31/01/2007

function date_out( date_input) as date
Dim date_input As String
Dim date_out As Date
Dim year_out As String
Dim month_out As String
Dim day_out As String

' extract the year
year_out = Mid(date_input, 1, 4)

' extract the month
month_out = Mid(date_input, 6, 2)

' extract the day
day_out = Mid(date_input, 9, 2)

' join the day, month and year to give 31/01/2007 using the
' sample date date_input
date_out = day_out & "/" & month_out & "/" & year_out

End function