how to get/ display the date in a particular format from sql

[Kingkumar]

Hi,

when i query my database i get this as value from teh data base(sql server 2000)

2004-06-16 11:09:15.253

now what i need to do is to display it in a particular format depending on the cultuer of the machine basically use the globalization but my question is how to do it ?
and also is there a way that i can modify it so that it appears as mmddyy hhmmss with (am/pm) only not the .253 from the database itself or i have to do it in the application.
thanks
regards,
king

 

[fredericofonseca]

Using SQL you can use the cast and/or convert function to modify the way date fields are returned to you.

Look on books online (supplied with the SQL Server install) for full usage.

The convert functions with AM/PM will also return the .253, but you can cut this by using cast.

But normally conversion on date formats should be done within the form objects, not the SQL, as this implies coding within the SQL.


There are API's to retrieve/convert date formats and they can also be used on your code.

If you look at

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/intl/nls_5w6s.asp

you will se a C example. Not exactly what you asked, but it should help you to dig through microsoft for more info.

I have also found this

http://www.i18nwithvb.com/

Not sure if is good, but may help.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[rsinj]

you can do it either from the application or if you're passing your query to the sql server, you can make your query do it.

however, as Frederico mentions, it would be a better task to do locally in the application since the result is going to be machine-specific.

use the Format function with "Short Date", "Medium Date", or "Long Date" - these will follow the settings on your Control Panel->Regional Settings which will follow the regional settings on each user's machine. however, you will need to split the date & time portions of your string at the space. handle the date portion with the format "Short Date", "Medium Date", or "Long Date" and then the time portion with "Short Time", "Medium Time", or "Long Time" and you'd need to strip the milliseconds.

so you could use something like this (for example):

Public Function FormatDate(s As String) As String
Dim iPos As Integer
Dim sDate As String
Dim sTime As String

  iPos = InStr(s, " ")
  sDate = Left$(s, iPos - 1)
  sTime = Mid$(s, iPos)
  
  iPos = InStr(sTime, ".")
  sTime = Left$(sTime, iPos - 1)
  
  FormatDate = Format$(sDate, "Short Date") & " " & Format$(sTime, "Medium Time")
End Function

?FormatDate("2004-06-16 11:09:15.253")
6/16/2004 11:09 AM


if you would like SQL Server to do it, read up on the Convert function where you would set the format code, and you'd be setting that in your VB app when sending the query or SP call to the SQL Server. but again, this is better handled in your app - you'd be doing lots more work having SQL server do it as your VB app would need to determine the format code - which would be more logic than the function above.

 

[rsinj]

if you want your custom format, change the format line in the above function to:

  FormatDate = Format$(sDate, "mmddyy") & " " & Format$(sTime, "hhmmss am/pm")

but, this is simply using your hardcoded format and not the regional/globalization settings.

 

[TNN]

If I'm understanding you correctly, try the DatePart function for SQL Server.
Tnn, Tom

TOM

http://WWW.TNPAYROLL@AOL.COM