SQL Server Query for take 10/27/2010 12:57:49 pm to 12:57 pm format

Question

0.00/5 (No votes)

See more:

Hi all Codeproject guys....

I have a datetime column in SQL Server 2000 that gives me data like this 10/27/2010 12:57:49 pm and I want to query this column just have return hh:mm pm like 12:57 pm. Which query I use to fetch this column. I am using this query in C# windows application.

Posted 20-Jul-13 8:26am

shunuman

Updated 24-Jul-13 21:35pm

v2

Add a Solution

Comments

[no name] 20-Jul-13 14:37pm

You don't format the data when you query it. You format the data when you display it.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Chui PuiKwan · Answer 1 · 2013-07-24T21:50:00

Solution 4

ur,you can try to run the following codes not by convert .

SQL

select count(*) from dbo.a
where RcvDate > '2013-07-15 16:00:00.000' and RcvDate <='2013-07-16 16:00:00.000';

Posted 24-Jul-13 21:50pm

Chui PuiKwan

Comments

shunuman 25-Jul-13 4:16am

Sorry, it shows Invalid column name 'RcvDate'.

Chui PuiKwan 25-Jul-13 4:43am

question：Table data like 7/15/2013 2:45:00 PM and I need 2:45 PM ？
answer：
1.handler the data like 7/15/2013 2:45:00 PM by c#
String[] str=7/15/2013 2:45:00 PM".split（' '）;
String time=str[1]; //get 2:45:00

int end =time.lastindexof（":"）;
time =time .substring (0,end )+str[2];

I code above codes by hand written ,so you need debug it in vs .

Chui PuiKwan 25-Jul-13 4:44am

2.handle data by sql convert

shunuman 25-Jul-13 4:18am

If I put my table instead of RcvDate then show 0 value.

shunuman 25-Jul-13 4:27am

Table data like 7/15/2013 2:45:00 PM and I need 2:45 PM
I am getting gata from table and write it text file, my code is like..

SqlDataAdapter sdr = new SqlDataAdapter("select * from currentdata", santosh.destination);
DataSet ds = new DataSet();
sdr.Fill(ds, "currentdata");
DataTable dt = new DataTable();
dt = ds.Tables[0];
int Rowcount = dt.Rows.Count;
TextWriter twr = new StreamWriter(@"D:\TIDS.txt");
int RowPos = 0;
while (Rowcount > 0)
{
twr.WriteLine("");
twr.Write("STA=" + dt.Rows[RowPos].ItemArray[4];// I want to convert this value in 2:45 PM
RowPos++;
Rowcount--;

}
twr.Close();
}

_Asif_ · Answer 2 · 2013-07-20T10:04:00

Solution 2

Check below query. Convert function will do the trick

SELECT convert(varchar, date_column, 114)
from Table

Posted 20-Jul-13 10:04am

_Asif_

Comments

shunuman 25-Jul-13 3:29am

thanks for reply but it gives 14:45:00:000 and I need 2:45 PM, Please give correct query.

_Asif_ 25-Jul-13 4:11am

SELECT CONVERT(VARCHAR(5), @date, 108) + ' ' +
SUBSTRING(CONVERT(VARCHAR(19), @date, 100),18,2)

shunuman 25-Jul-13 4:14am

Sorry, it's not working.

shunuman 25-Jul-13 4:29am

Table data like 7/15/2013 2:45:00 PM and I need 2:45 PM
I am getting gata from table and write it text file, my code is like..

SqlDataAdapter sdr = new SqlDataAdapter("select * from currentdata", santosh.destination);
DataSet ds = new DataSet();
sdr.Fill(ds, "currentdata");
DataTable dt = new DataTable();
dt = ds.Tables[0];
int Rowcount = dt.Rows.Count;
TextWriter twr = new StreamWriter(@"D:\TIDS.txt");
int RowPos = 0;
while (Rowcount > 0)
{
twr.WriteLine("");
twr.Write("STA=" + dt.Rows[RowPos].ItemArray[4];// I want to convert this value in 2:45 PM
RowPos++;
Rowcount--;

}
twr.Close();
}

RedDk · Answer 3 · 2013-07-20T09:00:00

In summary:

SELECT 
     CAST('2003-10-03 15:15:59. 0013621 +00:00' AS time(7)) AS 'time', 
      CAST('2003-10-03 15:15:59. 0013621 +00:00' AS date) AS 'date', 
       CAST('2003-10-03 15:15:59.153' AS smalldatetime) AS 
        'smalldatetime', 
         CAST('2003-10-03 15:15:59.153' AS datetime) AS 'datetime', 
          CAST('2003-10-03 15:15:59. 0013621 +00:00' AS datetime2(7)) AS 
        'datetime2',
           CAST('2003-10-03 15:15:59.0013621 +00:00' AS datetimeoffset(7)) AS 
        'datetimeoffset';

Pick any one, or more, of six suitable.

time date smalldatetime datetime datetime2 datetimeoffset
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
15:15:59.0013621 2003-10-03 2003-10-03 15:16:00 2003-10-03 15:15:59.153 2003-10-03 15:15:59.0013621	2003-10-03 15:15:59.0013621 +00:00

SQL Server Query for take 10/27/2010 12:57:49 pm to 12:57 pm format

3 solutions

Solution 4

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us