Which query I use to fetch datetime column in SQL Server2000

Question

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.

Riyaz Akhtar · 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 TrainNoTrainNameETrainNameHRIGHT(CONVERT(VARCHAR CAST(STA as datetime) 100)7) AS STADLYPlatNoPf_endSTSCoach_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.

Riyaz Akhtar · Answer

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.

Santosh Kumar · Answer

Sorry it gives same output like 3:00PM 4.25PM.

Sanjeeb Lenka · Answer

try this query for 24 hr format select TrainNoTrainNameETrainNameH RIGHT(CONVERT(VARCHAR CAST(STA as datetime) 108)8) AS STADLYPlatNoPf_endSTSCoach_sequence from currentdata

Santosh Kumar · Answer

Can you please tell me if I want in 24 hour time like 17:45 not AM or PM then how to convert.

Santosh Kumar · Answer

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.

Santosh Kumar · Answer

Thanks a lot Mr. R. A. it's working well. So thank you again for giving your precious time for me.

Riyaz Akhtar · Answer

Not able to download:-(.

Iftikar Hussain · Answer

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

Santosh Kumar · Answer

Sorry I attached again.

Riyaz Akhtar · Answer

i think u have missed the attachment.

Iftikar Hussain · Answer

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

Santosh Kumar · Answer

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.

Riyaz Akhtar · Answer

try below script: SELECT RIGHT(CONVERT(VARCHAR CAST('10/27/2010 13:57:49' as datetime) 100)7) AS CurrentTime

Sanjeeb Lenka · Answer

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/

Jignesh Trivedi · Answer

hi I think using Dataname you can achieve this select DATENAME(HH GETDATE()) + ':' + DATENAME(MI GETDATE()) hope this will help you.