SQL server dates in different formats

Here is the list of conversions that facilitates retrieving date time in different formats in T - SQL.

Replace Date in Convert with your field Name.

'YYYYMMDD'

CONVERT(CHAR(8), Date, 112)

'YYYY-MM-DD'

CONVERT(CHAR(10), Date, 23)

'YYMMDD'

CONVERT(VARCHAR(8), Date, 12)

'YY-MM-DD'

STUFF(STUFF(CONVERT(VARCHAR(8), Date, 12),
5, 0, '-'), 3, 0, '-')

'MMDDYY'

REPLACE(CONVERT(CHAR(8), Date, 10), '-', SPACE(0))

'MM-DD-YY'

CONVERT(CHAR(8), Date, 10)

'MM/DD/YY'

CONVERT(CHAR(8), Date, 1)

'MM/DD/YYYY'

CONVERT(CHAR(10), Date, 101)

'DDMMYY'

REPLACE(CONVERT(CHAR(8), Date, 3), '/', SPACE(0))

'DD-MM-YY'

REPLACE(CONVERT(CHAR(8), Date, 3), '/', '-')

'DD/MM/YY'

CONVERT(CHAR(8), Date, 3)

'DD/MM/YYYY'

CONVERT(CHAR(10), Date, 103)

'HH:MM:SS 24'

CONVERT(CHAR(8), Date, 8)

'HH:MM 24'

LEFT(CONVERT(VARCHAR(8), Date, 8), 5)

'HH:MM:SS 12'

LTRIM(RIGHT(CONVERT(VARCHAR(20), Date, 22), 11))

'HH:MM 12'

LTRIM(SUBSTRING(CONVERT(
VARCHAR(20), Date, 22), 10, 5)
+ RIGHT(CONVERT(VARCHAR(20), Date, 22), 3))

For more SQL tips and tricks , Subscribe here or click here to get updates via email

Read More

How to enter free text in a ajax asp.net combo box ?

A day before I had requirement where the user wants a combox box that gives him access to enter free text also inside it. Thanks to Ajax Control Toolkit which is a open source that lets us modify the code according to our requirements . So How do we let the user enter free text in a ASP.NET AJAX combo box ?

1. Download the source code for AJAX control toolkit from here .
2. Once you open the solution, Under Ajax Control toolkit you will find a folder 'ComboBox'
3. In that try to open Combobox.debug.js . If you dont have it , open Comboxbox.js .
4. Now try to find the following piece of code in it and comment it.

if (this.get_selectedIndex() == -1 && this.get_dropDownStyle() == AjaxControlToolkit.ComboBoxStyle.DropDownList) {
this.get_textBoxControl().value = '';
e.preventDefault();
e.stopPropagation();
return false;
}

This code is basically responsible to prevent entering free text when you dont have any item in the list that matches with what you had typed.

5. Now build the solution, add a reference to the dll , add the combo box control to your web page and you should be able to enter free text in ComboBox.

If you encounter any errors, throw a comment.I will help you out.

Want to know more on ASP.NET ajax, Subscribe here or click here to get updates via email

Read More

How to set date time as text to a excel in DD/MM/YYYY format using ASP.NET ?

Did you ever try to set a date as text to Excel cell using asp.net ? I had this interesting problem today. The date that I have been passing from my code is something like this '02/06/2009' (dd/mm/yyyy) .But When I check the Excel it appeared as '06/02/2009' (mm/dd/yyyy) . Dates with date > 12 will appear correctly in the excel . I tried by setting the format of cells to dd/mm/yyyy but no luck. So How will we achieve it ?

Solution :

To avoid this problem all you need to do is append a single quote (') before your date . i.e '02/06/2009 should be set as text to excel and you will see the date in correct format.

For more solutions to day to day ASP.NET problems , Subscribe here or click here to get updates via email

Read More