SQL date format help

  • Thread starter Thread starter Martin
  • Start date Start date
M

Martin

Hi everyone,

I would like to know how to format the date anyway I like.

The problem is this:

My SQL server is configured to use the mm/dd/yyyy format, the network server
also uses this format.

The problem is that some workstations are configured to use the dd/mm/yyyy
format and some use the mm/dd/yyyy then agian some use the dd-mmm-yyyy
format. I would like to keep these formats on the workstation.

What I would like is for my adp users to login to any workstation and access
the SQL server.

Is there a way to format the date so it will always be mm/dd/yyyy no matter
the settings on the PC.

I've tried using the format function, but it didn't work.

Any suggestions would be greatly appreciated.

TIA,

Martin.
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

SQL Server can also use YYYYMMDD format.

All dates are stored as floating point numbers. The integer part is the
number of days since X and the decimal part is the number of
milliseconds since midnight for the indicate day. In Access tables the
first day, day X, or day ZERO, is Dec 30, 1899. In SQL'r tables the
first day is Jan 1, 1900.

If sending a query to SQL'r using VBA you can use the format function
like this (running from a form w/ the date in a TextBox):

dim strWhere as string
strWhere = "WHERE DateColumn = '" & Format(Me!txtDate,"YYYYMMDD") & "'"

This assumes there is a valid date in the TextBox txtDate.

If the value in Me!txtDate were 21/4/04, the result of the concatenation
would be:

WHERE DateColumn = '20040421'

The string date needs to be delimited by single-quotes.

HTH,

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQIbrTYechKqOuFEgEQKYzgCfVxD6q+mjgOGrW6bzheGMVXbvY6oAoNJf
cD+RGxnrpAbbmJhrmpyPjZMQ
=inGT
-----END PGP SIGNATURE-----
 
Thanks I'll try it.

Regards,

Martin.


MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

SQL Server can also use YYYYMMDD format.

All dates are stored as floating point numbers. The integer part is the
number of days since X and the decimal part is the number of
milliseconds since midnight for the indicate day. In Access tables the
first day, day X, or day ZERO, is Dec 30, 1899. In SQL'r tables the
first day is Jan 1, 1900.

If sending a query to SQL'r using VBA you can use the format function
like this (running from a form w/ the date in a TextBox):

dim strWhere as string
strWhere = "WHERE DateColumn = '" & Format(Me!txtDate,"YYYYMMDD") & "'"

This assumes there is a valid date in the TextBox txtDate.

If the value in Me!txtDate were 21/4/04, the result of the concatenation
would be:

WHERE DateColumn = '20040421'

The string date needs to be delimited by single-quotes.

HTH,

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQIbrTYechKqOuFEgEQKYzgCfVxD6q+mjgOGrW6bzheGMVXbvY6oAoNJf
cD+RGxnrpAbbmJhrmpyPjZMQ
=inGT
-----END PGP SIGNATURE-----
Click to expand...
 
Back
Top