Question about Microsoft SQL Server 2005 Standard Edition

1 Answer

SQL query to change date and/or time format

Can I do a SQL query to change the format of how the date (& time in a different field) is displayed before I pull it back into my VS 2010 .net reports? I just want a short date (& short time). Can someone provide an example? Thanks for any assistance.

Posted by on

Ad

1 Answer

  • Level 1:

    An expert who has achieved level 1.

    Corporal:

    An expert that hasĀ over 10 points.

    Mayor:

    An expert whose answer gotĀ voted for 2 times.

    Problem Solver:

    An expert who has answered 5 questions.

  • Contributor
  • 16 Answers

The function you're probably looking for is CONVERT. Here's an example (101 applies the format "mm/dd/yyyy", 108 applies "hh:nn:ss"):

SELECT CONVERT(varchar(10),getdate(),101) as JustDate, CONVERT(varchar(8), getdate(), 108) as JustTime


Results:

JustDate JustTime
---------- --------
09/24/2013 13:13:56

(1 row(s) affected)

Posted on Sep 24, 2013

Ad

6 Suggested Answers

6ya6ya
  • 2 Answers

SOURCE: I have freestanding Series 8 dishwasher. Lately during the filling cycle water hammer is occurring. How can this be resolved

Hi,
a 6ya expert can help you resolve that issue over the phone in a minute or two.
best thing about this new service is that you are never placed on hold and get to talk to real repairmen in the US.
the service is completely free and covers almost anything you can think of (from cars to computers, handyman, and even drones).
click here to download the app (for users in the US for now) and get all the help you need.
goodluck!

Posted on Jan 02, 2017

Ad
dbarchitect
  • 150 Answers

SOURCE: Help me..

It needs to be in the format "yyyy-dd-mm hh:mm:ss:nnn". The BETWEEN keyword must include the time as well. so if you want from DTPicker date of "2007-09-01" and "2007-09-10" it will literally be this "2007-09-01 00:00:00.000" and "2007-09-10 00:00:00.000" thus not including any of the day for the 10th. If you want the 10th, then you have increase Date2 + 1 day to be "2007-09-11 00:00:00.000".

You should always remember that dates include the time.

I like to use the >= date1 and < date2. It's a little more forgiving.

Posted on Nov 24, 2007

SOURCE: Connecting of SQL Server and .net

winxp

Posted on Jan 14, 2008

mcc99
  • 51 Answers

SOURCE: Microsoft Access Query

Your date form is what it wrong. In order to use dates in Access with a BETWEEN function clause the values have to specifically be dates.

You can create dates in literal strings using the # sign to bound them, as in:

Select DISTINCT [ActivityDate] between #1/1/08# and #12/31/08#

#....# tells Access to treat this literal string as a date.

If you are using non-literal values, such as a parameter handed into the query, which would then be a variable, you can't use #some_variable# to cause Access to view it as a Date since #...# is for literal values only. To convert (also called "casting" a variable), use the CDate function. CDate likes to know what format to expect the string to come in though so it helps to pass that along, as in:

Select DISTINCT [ActivityDate] between CDate(begin_dt_string) and CDate(end_dt_string)

Remember the date string you pass into CDate has to be readable as a date by CDate. So make sure you pass in something that it can handle (use the IsDate() function to check to be sure so you can avoid a runtime datatype casting error). If your locale is US, then passing in x/y/z it will take for MM/DD/YYYY. If Europe, it will take it as DD/MM/YYYY. So keep that in mind.

To convert out of a date, use the FORMAT() function. That is how you get a date data type value to become a string value.

More fun reading:
http://www.techonthenet.com/access/functions/datatype/cdate.php
http://www.techonthenet.com/access/functions/advanced/isdate.php
http://www.techonthenet.com/access/functions/date/format.php

Hope this helps!

Posted on Feb 23, 2008

robjac
  • 109 Answers

SOURCE: Conditional Formatting doesn't work until I select the item

the mouser is equip with shortcut of any of program by using right click pointing to item.

Posted on Oct 18, 2008

  • 2 Answers

SOURCE: Running SQL queries on Excel

Another way to do it is to use the SQL Drill freeware Excel addin (http://www.sqldrill.com)
hth

Posted on Apr 21, 2009

Add Your Answer

Uploading: 0%

my-video-file.mp4

Complete. Click "Add" to insert your video. Add

×

Loading...
Loading...

Related Questions:

1 Answer

How do you change the time on a dymo datemark


Hi there!
Here's what you can do"
1. To set the current date and time, press and hold down the SET/EDIT button for a few seconds. Release the SET/EDIT button when the Month (M) and Day (D) indicators start flashing.
Note: The unit will revert back to normal display if no other key is pressed for 10 seconds
2. While the Month (M) and Day (D) indicators are flashing press the LOCK button to cycle through the 3 available date formats.
3. Once you have chosen an appropriate date format press the SHIFT button to switch between each of the different DATE, MONTH, YEAR, HOUR, and MINUTE fields. The field that is currently active will start flashing. To change the field's value press the LOCK button. Each stroke of the LOCK button moves one value up.
Note: If you go past the current year, you will need to press the LOCK button to cycle through the year 2189 and back up to the current year.
4. Once you are finished setting the Date and Time press the Set/Edit button to return to the normal display. The Date/Time Stamper is now ready to print..
Hope this helps!
Vikroks

Mar 29, 2011 | Dymo DateMark Date/Time Stamper

1 Answer

How do I set date and time?


  1. To set the current date and time, press and hold down the SET/EDIT button for 2 seconds, let go and the Month (M) and Day (D) indicators will start flashing.
  2. While the Month (M) and Date (D) indicators are flashing press the LOCK button to cycle through the 3 available date formats.
  3. Once you have chosen an appropriate date format, press the SHIFT button to switch between each of the different DATE, MONTH, YEAR, HOUR and MINUTE fields. The field that is currently active will start flashing. To change a field's value press the LOCK button, each stroke of the Lock button moves one value up.
  4. Once you are finished setting the Date and Time press the SET/EDIT button to return to normal display.
Note: If you go past the current year, you will need to press the LOCK button to cycle through the year 2189, and back up to the current year. Be cautious when setting the year
Resource 1: Dymo Date Mark Electronic Date/Time Stamper Manual

Feb 17, 2011 | Dymo Date Mark Electronic Date/Time...

1 Answer

Need to reset date and time after changing the


Here's the instructions on how to do that :
Quick Instructions on Setting the Date/Time:
  1. To set the current date and time, press and hold down the SET/EDIT button for a few seconds. Release the SET/EDIT button when the Month (M) and Day (D) indicators start flashing.
    Note: The unit will revert back to normal display if no other key is pressed for 10 seconds.
  2. While the Month (M) and Day (D) indicators are flashing press the LOCK button to cycle through the 3 available date formats.
  3. Once you have chosen an appropriate date format press the SHIFT button to switch between each of the different DATE, MONTH, YEAR, HOUR, and MINUTE fields. The field that is currently active will start flashing. To change the field's value press the LOCK button. Each stroke of the LOCK button moves one value up.
    Note: If you go past the current year, you will need to press the LOCK button to cycle through the year 2189 and back up to the current year.
  4. Once you are finished setting the Date and Time press the Set/Edit button to return to the normal display. The Date/Time Stamper is now ready to print.
The manual for this device can be found here :
http://images.cableorganizer.com/dymo-label-printer/DateMark_Time-Manual.pdf

Oct 26, 2009 | Dymo DateMark Date/Time Stamper

3 Answers

How reset time?


Set the date and time
You can set the date and time from the control panel. When a fax is transmitted, the
current date and time are printed along with the fax header. The date and time format
is based on the language and country/region setting.
Note If your HP all-in-one loses power, you will need to reset the date and time.
1 Press Setup.
2 Press 4, and then press 1.
This selects Basic Fax Setup and then selects Date and Time.
3 Enter the month, day, and year by pressing the appropriate numbers on the
keypad. Depending on your country/region setting, you might enter the date in a
different order.
The underline cursor is presented initially beneath the first digit; it automatically
advances to the next digit when you press a button. The time prompt
automatically appears after you enter the last digit of the date.
4 Enter the hours and minutes.
5 If your time is displayed in the 12-hour format, press 1 for AM, or 2 for PM.
The new date and time settings appear on the control panel display.
Regards...

May 27, 2009 | HP OfficeJet 6210 All-In-One InkJet...

1 Answer

How do i use date in visual basic 6


Hi,

I am not getting your problem clearly.
If you are referring to save the input dates as date in database with the help of sql query then you need to provide hash(#) mark at the beginning and end of date values. It will resolve your problem.

Hope this helps and let me know if you are not getting the solution clearly.

Thanks.

Mar 06, 2009 | Microsoft Visual Basic 6.0 for PC

1 Answer

How to create a multiple access query and put altogether into 1 SQL Scrip(.sql file)


Suggest using Union Query. Thus, you can add more queries from many table and the output will be in one (1) query. Bear in mind that all fields must have same Field Name.

For example:
tblOne contains dtDate1 fieldname. Your query will select [tblOne].[dtDate1] As myDate.
tblTwo contains dtDate2 fieldname. Your query will have the following SQL:

Select [tblOne].[dtDate1] As myDate
UNION Select [tblTwo].[dtDate2] as myDate

Thus the output will result dates from tblOne and tblTwo in a column called myDate.

Regards,
Maha

Feb 05, 2009 | Microsoft Computers & Internet

1 Answer

Xerox workcentre Pro 575 How do I change the time of day setting


  1. From the Standby Mode, press the [Menu] (up arrow) button, then press [4], [2]. The screen will display: Date.
    [01-31-2000]
  2. Use the numeric keypad to enter the date and the arrow buttons to move between the fields, then press [Enter]. The screen will display:Date format.
    1. MM-DD-YYYY
    2. DD-MM-YYYY
    3. YYYY-MM-DD
  3. Use the numeric keypad to designate the Date format you require to use. The screen will display: Month format.
    1. Numeric: displays the month with a number
    2. Name: displays an abbreviated month name.
  4. Use the numeric keypad to designate the Month format you require to use. The screen will display: Time format?
    1. 24 hour
    2. 12 hour
  5. Use the numeric keypad to designate the time format you require to use. The screen will display: Time.
    10:30 pm (or 22:30 pm if you chose the 24 hour format).
  6. Use the numeric keypad to enter the time and the arrow buttons to select [AM] or [PM], then press [Enter].
  7. Press [Exit] or [Stop] when the menu selection screen displays to return to the Standby Mode.

Nov 20, 2008 | Xerox WorkCentre Pro 575 Plain Paper Laser...

2 Answers

Reset time


  1. To set the current date and time, press and hold down the SET/EDIT button for a few seconds. Release the SET/EDIT button when the Month (M) and Day (D) indicators start flashing.
    Note: The unit will revert back to normal display if no other key is pressed for 10 seconds.
  2. While the Month (M) and Day (D) indicators are flashing press the LOCK button to cycle through the 3 available date formats.
  3. Once you have chosen an appropriate date format press the SHIFT button to switch between each of the different DATE, MONTH, YEAR, HOUR, and MINUTE fields. The field that is currently active will start flashing. To change the field's value press the LOCK button. Each stroke of the LOCK button moves one value up.
    Note: If you go past the current year, you will need to press the LOCK button to cycle through the year 2189 and back up to the current year.
  4. Once you are finished setting the Date and Time press the Set/Edit button to return to the normal display. The Date/Time Stamper is now ready to print.

Nov 05, 2007 | Dymo DateMark Date/Time Stamper

3 Answers

Viewing ms Sql Server database on Access


Are you familiar with "Linking" tables in Access? In Access: File --> Get External Data --> Link Tables. This will bring up a dialog box which allows you to browse to your SQL server and select the table you want.

Aug 23, 2007 | Microsoft SQL Server Standard Edition for...

4 Answers

Running SQL queries on Excel


Another way to do it is to use the SQL Drill freeware Excel addin (http://www.sqldrill.com)
hth

Aug 14, 2007 | Microsoft Office 2003 Basic Edition...

Not finding what you are looking for?
Microsoft SQL Server 2005 Standard Edition Logo

Related Topics:

90 people viewed this question

Ask a Question

Usually answered in minutes!

Top Microsoft Computers & Internet Experts

micky dee

Level 3 Expert

2774 Answers

Piyal Perera
Piyal Perera

Level 3 Expert

528 Answers

Les Dickinson
Les Dickinson

Level 3 Expert

18394 Answers

Are you a Microsoft Computer and Internet Expert? Answer questions, earn points and help others

Answer questions

Manuals & User Guides

Loading...