with the bcp (bulk copy program) I output some data to a file. The data consists of two columns which are datetime values and one column with the difference in time. For the difference calculation I use the DATEDIFF function. This function can only give the diff back in minutes or hours and not in a time format.
Can I use a function to format the result or do I have to do it manually?
I managed to do the calculation manually by using the % (mod) operator, but then I have the time in this format 0:0 where it should be in 00:00 or 00:00:00. In that case I need a function or a way to add preceding zero's to the values.
Note that the formatting or manual calculation should be done in the SELECT clause to be able to use the bcp tool.
I am in need of a format string or simple vb code that can add the local time zone to the end of a time field.. Something like: 9:36 AM EST 9:36 AM PST
This timezone will just come from the machine that the reporting server is on. I don't see this listed as a standard format, and I have come up empty so far in my research - anyone got a solution for this?
I have this piece of SQL code coming from Access: Format([Jobnumber2],"00000") AS [Job Number] What is the translation in SQL server language? I can't find the equivalent of the Format function! Otherwise, is there a way to use ASP code to format the number in the way I'd like to?
Hi ... i'm sorry to hassle this user group but i have an urgent needfor some code - i have tried and tried to find a solutionn elsewhere -but the problem is i don't really know what i am looking for.What is the equivalent SLQ code for the below statement which works inMS Access?Format(7,"00")Result: 07or alternatively ...what i am actually trying to do is return the string yyyymm based onthe current date. eg 200506 (June 2006)Is there a simple way of doing this?The code i am currently using isCONVERT (char, DATEPART(yyyy, GETDATE())) + CONVERT (char, DATEPART(mm,GETDATE()))Result: 2005 !!!!At least i thought i would get 20056. But obv what i am aiming for is200506.Thanks in advance.TC
HI,I'm pretty new to MS SQL,My problem is setting ....=Format$(Date(),"mm" & "/1/" & "YYYY")(Which is from my MS ACCESS database)as my default value in MS SQL. How can I do this since format() doesnot seem to be a function that is in MS SQL. The above function worksgreat in MS ACCESS, but not in MS SQL.thanks
Hello, i don't know how to format a string with an SQL select queryfor my VB6 App.I have a table like this :Code - Name1 - Jonathan2 - Mike....9 - Claudia10 - Robbie11 - SandyBut I would get code column result's with a particular format likethis :0001 - Jonathan0002 - Mike....0009 - Claudia0010 - Robbie0011 - SandyI use the Format(column, "#0000") function in my application ffor themoment but nothing to do with the DB Engine side ???I tried CONVERT function :SELECT CONVERT(varchar(4), code, '0000') FROM Employes;But the code result's stil 1,2,3 and not 0001,0002,0003 !!!Anyone has the solution ?ThanksJonathan
I am looking for a way to retrieve the time from a Sql 2000 database in the format of hh:mm AM or PM. I am storing the information as a "datetime" data type. If I only supply the time (such as 02:30 PM) then the date defaults to 12/30/1899. When I read the information from the database I get "1899-12-30 14:30:00:000".
If I enter 02:30 PM I would like to get 02:30 PM.
I have tried various convert functions in Sql, maybe there is a way to format with an ASP.Net datetime function but I need help to get the information back in the right format. Thx In Advance
I am taking datetime value from a table MA and separating date and time by using CONVERT function.
CONVERT(VARCHAR(12), MA.ArrivalDate, 101) AS ArrivalDate, ' CONVERT(VARCHAR(12), MA.ArrivalDate, 108) AS ArrivalTime,
but my ArrivalTime is coming as 13:01:00. I want to convert this time as 1:01:00. I have to do this conversion in my stored proc, not in my code. can you help ..? Thanks
we have a column in database named "time" but it stores value as VARCHAR(9) datatype, when i display the "time" field (let us say "time" value is 07:54 AM) on reports, it displays the value as it is (07:54 AM) but i need the value to be displayed as 7:45 AM on the reports.
how can i do that ? clearly
field in the database is TIME (varchar(9)) 07:45 AM must be displayed as 7:45AM 12:34 PM must be displayed as 12:34 PM I tryed to write a function in sql reports properties code which acceptes array of charecters and depending up on the value of left most charecter of recieved array it will return charectr string back, but coulded get it worked
I am moving the database built in access to Sql 7 and i am unable to find any subsitute of format function of Access in sql. Please help me out ot find a suitable solution of it.
Hi, I came upon a strange behavior of Format function in report, which I'm unable to explain.
I have some double value, which I want to format. E.g. the value is in db field "Users" and is 303870
1) If I set a Value property for the field to =Fields!Users.Value or CDec(=Fields!Users.Value) //the value is already double so the CDec has no effect
and Format property for the field to =Format(me.Value,"#,#") or =Format(Fields!Users.Value,"#,#") than the number which is visible in the report is 3303,873870 ???
2) If I put in the Value property =Format(Fields!Users.Value,"#,#")
and the Format property is empty than the output is ok 303,870, however this is not desired, because the value is than handled as string
3) If I put in the Value property =Fields!Users.Value
and into Format property =Format(CStr(me.Value),"#,#") than the output is ok again.
As I understand, the Format should be same as in VB (I am actually C# programmer, not very familiar with VB) so I tried to use Format in VB on double value, and the output was as expected (e.g. 303,870), but when I used to a string I got only the style (e.g. #,#).
So I wonder, the ReportingServices Format function works correctly only with string input? But why than works the example 2)? Or do I have somewhere a mistake?
Hi, I want ot get time just like this : 8:40:15 AMI am using select convert(varchar(12),getdate(),8) to get this but this function return 24 Hrs format. How to achive this by using small coding. Jasim...
I have a sql table setup with a date field and a time field. They are both set to datetime.
In my net form, the date is entered as a date and gets added the the SQL table OK.
My problem is the time field. If i enter it straight into the SQL table as say 2pm, it adds it correctly as 2:00:00 PM. When I try to enter the time through my VB.net webform, the time gets added ok, but it places the date in the field as well to look like 14/04/2004 2:00:00 PM, I only want the time part of the field.
My code to add the time to the SQL table is as follows,
When I edit the recurring job schedule for a job, I try to set a job run in daily frequency at 11:00 am . But I can only edit the field in "11:00:00" and it always run at 11:00pm. Can someone know to set it run at 11:00am.
In SQL Server 2000 we are loading data into a smalldatetime field. The file is being loaded from a text file that contains the date in the following format: 07/24/2000 16:08
The problem occurs when the data is as follows: 04/28/2000 14:60
Microsoft SQL Server6.5 was forgiving and would translate this to 15:00 upon load but SQL Server2000 is failing with the Invalid Time Format error.
Can anything be done to force SQL Server 2000 to recognize this?
What do you think of a query that generates aSystem.Data.SqlClient.SqlException when submitted via a application,but when run through QueryAnalyzer or EnterpriseManager doesn'tgenerate a error? Here are 2 examples of the query:SELECT table1.*,table2.field1,table2.field2,table2.field3 FROMtable1,table2 WHERE table1.field1 = table2.field1 ANDtable1.field2='103' ANDtable2.TStamp > '12/19/2005 12:20:14 PM' ORDER BY table2.TStampSystem.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '12'.SELECT table1.*,table2.field1,table2.field2,table2.field3 FROMtable1,table2 WHERE table1.field1 = table2.field1 ANDtable1.field2='103' ANDtable2.TStamp > '12/8/2005 8:29:43 AM'ORDER BY table2.TStampSystem.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '8'.I believe the error is referencing the hour portion of the timestamp.When I run these queries through QA/EM I don't get a result set ... somaybe those apps just trap the exception and ignore it. Still, seeanything wrong with the TStamp?
I have a DateTime parameter called End Date set up with a default value eg =DateAdd("s",-1,Today). When I preview, the default value appears as 19/09/2006 23:59:59, which is fine.
However, when the report is deployed, it is displayed as 19/09/2006 11:59:59 PM. This would also be fine, except when the user changes the date using the calendar, it converts the time back to 24 hour time - eg 19/09/2006 23:59:59. Then when the report is run, it converts the time format back to AM/PM again.
Also, I have a Start Date Parameter, with a default value of 12:00AM today. I would like this to be displayed as 19/09/2006 12:00AM in the Start Date parameter text box, however reporting services will not display the 12:00AM part. Using FormatDateTime in the Default value will return a string and cause an error. I don't want to change the parameter to a string and lose the date picker.
Any ideas,
Thanks
PS Getting the Date/Time settings changed on the reporting server would be extremely difficult!
declare @deadline Datetime = '2014-03-23 15:30:10.000' SELECT CONVERT(VARCHAR(30),@deadline, 100) AS DateConvert ----With this I am able to produce that like ----o/p: Mar 23 2014 3:30PM
I want to convert a datetime type into and speciically formatted time:the table contains this:1899-12-30 10:00:00.000I want to reformat it to appear like this:10:00 AM I want to do this with SQL using a CONVERT of something along that line.I've been able to find all kinds of date and date/time formats, but not time alone in the above format.And suggestions?TIA</chaz>
I have a customer time duration format like "12:15" that means 12 minutes and 15 seconds. I want to convert to be "12.25 " that means 12.25 minutes. Any suggestion please. Thanks a lot.
I have a table where the date and time stamp are logged together and I want to only show the date in a 10 character output. Also I want to return as blank some dates in the field that are 01/01/1800.
The current table format is 2013-06-28 00:00:00:000..I just want the date. I was using RTRIM function but it keeps erroring out.
Would it be possible to change the date time from varchar to datetime. it currently showing as varchar in the following format 20080401 0845 can it be changed to date/time format into something like this 01/04/2008 08:45. If someone could help that would be great. Many thanks
if now the system time is 2014-12-23 23:45:345 . then I want to calculate the sales amount with total and get current date and current hour filter by hh:00-hh:59 , my desire output is like below :
Current time:23:50 run this query Date Time amount 2014-12-23 23:40 $10 2014-12-23 23:01 $5 2014-12-23 23:39 $5 Total :$20
