Which query I use to fetch datetime column in SQL Server2000

Santosh Kumar

13y2.8k

Hi all !

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.

16 Replies

Know the answer? Post it — somebody with the same question will find it here.

Riyaz Akhtar13y ago
Accepted answer
Hi Santosh,

I have modified TxtGen method, also i have modified ur sql query, please correct according to ur table schema. here STA i have considered the column which u want to get the value in AM/Pm format.

private void TxtGen()
{
SqlDataAdapter sdr = new SqlDataAdapter("select TrainNo,TrainNameE,TrainNameH,RIGHT(CONVERT(VARCHAR, CAST(STA as datetime), 100),7) AS STA,DLY,PlatNo,Pf_end,STS,Coach_sequence from currentdata", destination);
DataSet ds = new DataSet();
sdr.Fill(ds, "currentdata");
DataTable dt =ds.Tables[0];
TextWriter twr = new StreamWriter(@"D:\TIDS.txt");
foreach (DataRow row in dt.Rows)
{

twr.WriteLine("");
twr.Write("TrainNo=" + row[0].ToString().Trim() + "|");
twr.Write("TrainNameE=" + row[1].ToString().Trim() + "|");
twr.Write("TrainNameH=" + row[2].ToString().Trim() + "|");

// This field I want to convert which ia in ItemArray[4]

twr.Write("STA=" + row[4].ToString() + "|");

twr.Write("DLY=" + row[6] + "|");
twr.Write("PlatNo=" + row[3].ToString().Trim() + "|");
twr.Write("Pf_end=" + row[7].ToString().Trim() + "|");
twr.Write("STS=" + row[8].ToString().Trim() + "|");
twr.Write("Coach_sequence=" + row[9].ToString().Trim());

}
twr.Close();
}

Let me know if u are still facing any issue.
0
Santosh Kumar13y ago
Sorry it gives same output like 3:00PM, 4.25PM.
0
Sanjeeb Lenka13y ago
try this query for 24 hr format

select TrainNo,TrainNameE,TrainNameH,RIGHT(CONVERT(VARCHAR, CAST(STA as datetime), 108),8) AS STA,DLY,PlatNo,Pf_end,STS,Coach_sequence from currentdata
0
Santosh Kumar13y ago
Can you please tell me if I want in 24 hour time like 17:45 not AM or PM then how to convert.
0
Santosh Kumar13y ago
Can you please tell me if I want in 24 hour time like 17:45 not AM or PM then how to convert.
If you know better link for study about these time conversion formulas, please provide.
I would be highly obliged and thankful for your help.
0
Santosh Kumar13y ago
Thanks a lot Mr. R. A. it's working well.

So thank you again for giving your precious time for me.
0
Riyaz Akhtar13y ago
Not able to download:-(.
0
Iftikar Hussain13y ago
did you try the query like this

select SUBSTRING(CONVERT(varchar, dt, 100), 13, 3)+SUBSTRING(CONVERT(varchar, dt, 100), 16, 2)+':00 '+
SUBSTRING(CONVERT(varchar, dt, 100), 18, 2) from currentdata

Regards,
Iftikar
0
Santosh Kumar13y ago
Sorry, I attached again.
0
Riyaz Akhtar13y ago
i think u have missed the attachment.
0
Iftikar Hussain13y ago
Hi,
Try like this

SqlDataAdapter sdr = new SqlDataAdapter("select SUBSTRING(CONVERT(varchar, dt, 100), 13, 3)+SUBSTRING(CONVERT(varchar, dt, 100), 16, 2)+':00 '+
SUBSTRING(CONVERT(varchar, dt, 100), 18, 2) from currentdata", destination);

Regards,
Iftikar
0
Santosh Kumar13y ago
Thanks For reply but it's not working properly. it gives STA=7/15/2013 2:45:00 PM.
My data fields as

TrainNo TrainName ArrTime and so on
12401 Magadh Express 7/15/2013 2:45:00 PM

Desired ArrTime in Text File as 2:45 PM Please Check C# code I have attached. Thanks a lot.
0
Riyaz Akhtar13y ago
I have modified ur code, plz check

here dt is the column name where datetime is stored.

SqlDataAdapter sdr = new SqlDataAdapter("select RIGHT(CONVERT(VARCHAR, CAST(dt as datetime), 100),7) AS dt from currentdata", destination);
DataSet ds = new DataSet();
sdr.Fill(ds, "currentdata");
DataTable dt = ds.Tables[0];
TextWriter twr = new StreamWriter(@"c:\TIDS.txt");
foreach (DataRow row in dt.Rows)
{
twr.WriteLine("");
twr.Write("STA=" +row["dt"].ToString());
}
twr.Close();

Please let me know if u r facing any issue.
0
Riyaz Akhtar13y ago
try below script:

SELECT RIGHT(CONVERT(VARCHAR, CAST('10/27/2010 13:57:49' as datetime), 100),7) AS CurrentTime
0
Sanjeeb Lenka13y ago
hi,
Refer to this link

http://blog.sqlauthority.com/2009/08/06/sql-server-get-time-in-hourminute-format-from-a-datetime-get-date-part-only-from-datetime/
0
Jignesh Trivedi13y ago
hi,
I think using Dataname you can achieve this
select DATENAME(HH ,GETDATE()) + ':' + DATENAME(MI ,GETDATE())
hope this will help you.
0

Which query I use to fetch datetime column in SQL Server2000

I would be highly obliged and thankful for your help.