[dba-SQLServer] FncStrDateSQL

Hi Asger and Borge

But none of these returns what Borge requests.

The YYYY-MM-DD HH:MI:SS(24h) ODBC Canonical is closer: 

  SELECT CONVERT(VARCHAR(19), GETDATE(), 120) 

Returns:

  1972-01-01 13:42:24 

If you insist on "/" as the date separator, you could wrap this in a
replace expression.

A list of other date formats:

  http://www.sql-server-helper.com/tips/date-formats.aspx 

/gustav


>>> ab-mi at post3.tele.dk 11-07-2008 15:44 >>>
Borge,

Haven't tried Gustav's function.
But in SQL Server you can use the session level option:
set dateformat mdy

Or you can use conversion:
convert(varchar(11),'your date',103) --> dd/mm/yyyy
convert(varchar(11),'your date',104) --> dd.mm.yyyy
convert(varchar(11),'your date',105) --> dd-mm-yyyy
convert(varchar(24),'your date',113) --> dd mon yyyy hh:mm:ss:mmm

Asger

-----Oprindelig meddelelse-----
Fra: dba-sqlserver-bounces at databaseadvisors.com 
[mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Borge
Hansen
Sendt: 11. juli 2008 03:35
Til: Discussion concerning MS SQL Server
Emne: [dba-SQLServer] [dba-sqlserver] FncStrDateSQL

Hi,
I was thinking: ... shall I search the archives ... or .... shall I
shoot a
quick message ...

What's the equivalent to Gustav's very helpful function (see below) of
formatting dates for use in queries when you are working with views /
sp in
SQL2005 ??

Public Function FncStrDateSQL(ByVal dat As Date) As String
' Formats full string of date/time in US format for SQL.
' Overrides local (non US) settings for date/time separators.
' Example output:    #08/16/1998 04:03:36 PM#
' 1999-10-21. Cactus Data ApS, CPH.
      FncStrDateSQL = Format(dat, "\#mm\/dd\/yyyy hh\:nn\:ss AM/PM\#")
End Function

I need to be able to do > ; >=; <; <= comparisons being secure in my
sleep
that a '7-Mar-2008' doesn't get interpreted as a '3-Jul-2008'

Gustav?

regards
Borge