i saw the previous query in which it replaces null values to '-'
i also want to do same but i have quantity column which is numeric(38,5) and i have to convert it first to varchar to replace null values of quantity - to '-'
select when quantity is null then cast(quantity as varchar)+ '-' else quantity from saleshistory
but still its getting nulls.
Hi all,
I have created a report in SSRS 2005 which is being viewed by users from different Time Zones.
I have a dataset which has a field of type datetime (UTC). Now I would like to display this Date according to the User Time Zone.
For example if the date is August 07, 2007 10:00 AM UTC,
then I would like to display it as August 07, 2007 03:30 PM IST if the user Time Zone is IST.
Similarly for other Time Zones it should display the time accordingly.
Is this possible in SSRS 2005?
Any pointers will be usefull...
Thanks in advance
sudheer racha.
cast([Birthdate]as datetime) [Birthdate], 01/12/2001
I have created this query below to change it to the format mmddyyyy.
I not sure were the birthdate goes at in this query.
replace(convert(varchar(10), getdate(), 101), '/', '')
Need getting the below Cursor query convert to a Recursive CTE or with a while loop as I do not want to use a cursor.
Declare @Companyname Nvarchar (400)
declare @str nvarchar(MAX)
TRUNCATE TABLE STAGING.dbo.[IT_G_L Entry]
DECLARE GLEntry_cursor CURSOR FOR
SELECT REPLACE Name FROM Company where Name <> 'AAAAA'
OPEN GlEntry_cursor
[Code] ....
Wanted to verify how I would run a replace...it's actually my first time doing a replace, but figure it's worse to screw this up than just a simple SELECT :)
The table/field is:
SOHeader.ShiptoID
I need to go through and replace only values which are equal to '9999' with the word 'DEFAULT' instead for all rows in that column.
I have two fields which im trying to convert to standard time. IE (09:05 PM) or (12:00 AM).
They are in Military format right now. so i have 15:45 and 21:30 etc etc.
They are both Chars. And its just times and no dates. Can anyone help??
select convert(varchar(16), getdate(), 101)+LEFT(REPLACE(convert(varchar, getdate(), 108), ':', ''),4)
From above query I get
mmddyyyyhhmm
but it' s yyyy and hour can not be separated
04/12/200702:05
How can I separated the year and hour ?
Thanks
Daniel
HI,
I AM HAVING A TABLE WHICH HAS INCREMENTAL COLUMNS,WHERE COLUMNS GETS ADDED EVERY MONTH TO THE TABLE AND THE TABLE THEN CONTAINS PREVIOUS MONTH AND PRESENT MONTH DATA ABOUT CUSTOMERS ,DETAILS AND TRANSACTIONS.
THE PROBLEM WITH THIS DATA IS ,IF THE CUSTOMER IS NEW ,THEN IN PREVIOUS MONTHS HIS INFORMATION IS NULL,WHICH HAVE TO BE CODED HAS "NOT PRESENT".
NOW,
HOW DO WE CONVERT ALL THE PREVIOUS COLUMNS FOR A PARTICULAR CUSTOMER HAS NULL AT THE SAME TIME ?.
HERE IS HOW THE PROC WRITTENED FOR IT GOES :-
DROP PROCEDURE DE_NAT
CREATE PROCEDURE DE_NAT
AS
BEGIN
DECLARE @MONMIN1 NVARCHAR(100),MON NVARCHAR(100),@YEAR NVARCHAR(100) , @MONYEAR NVARCHAR(100)
SET @MONMIN1 = DATENAME((MONTH),DATEADD(MONTH,-1,GETDATE()))
SET @MON = MONTH(GETDATE())
SET @YEAR = YEAR(GETDATE())
SET @MONYEAR = @MON + @YEAR
EXEC('select A.CUSTOMERS,B.*,CAST(A.RFM_40D AS FLOAT) AS R40
INTO TSD_' + @MONYEAR
+ ' from TSD_20 A
LEFT OUTER JOIN SD20 ' + @MONMIN1 + ' B
ON A.CUSTOMERS = B.CUSTOMER')
END
THIS PROC JUST ADDS THE PRESENT MONTHS DATA TILL LAST MONTHS DATA.
BUT IF A CUSTOMER IS NEW, THEN HOW DO I REPLACE THE NULL VALUES FOR THE PREVIOUS DATA TO 'NOT PRESENT'
FOR EG :- IF THERE IS A NEW CUSTOMER ,HOW DO WE CHANGE :-
CUSTOMERS ERTYYTRE RTYUUYTR TYUIIUYT QWERREWQ DFGHHGFD
----------- ---------- ---------- ---------- ----------- ----------
101023 <NULL> <NULL> <NULL> <NULL> 1.0
102022 1.0 1.62.3 3.4 4.5
NOW, AS YOU CAN SEE, THAT FOR CUSTOMERS = '101023'.
THE COLUMN DFGHHGFD IS, THIS MONTHS DATA , I WANT TO CHANGE ALL NULL VALUES PRESIDING IT AS "INACTIVE"
CAN I CHANGE , ALL COLUMNS FROM NULL TO "INACTIVE" , AT THE SAME TIME. ?
AS NEXT MONTH, AGAIN THE COLUMNS IS GONNA INCREASE WHICH WILL
AGAIN CAUSE A PROBLEM .
PLS TELL ME A METHOD , SO THAT I CAN DO THE NEEDFUL.
I need to convert a a string column to integer. Before converting, I need to check if it has blank values then convert it to NULL. Someone told me that its easier to convert it to NULL before converting to integer.
View 5 Replies View RelatedHi all.
I have posted this question in another forum but no one has so far been able to provide a solution to the problem. Since I know Database Journal always has very informative and enlightening posts, I figured I'd post the question here in hopes that some guru can provide an answer.
If you're running SQL 2000 and have any jobs that have been executed, you could perform a query as such:
select last_run_time from msdb.dbo.sysjobsteps
and receive returned values that contain the last time a job was executed "stored in integer datatype" columns. See ->
sp_help sysjobsteps.
In SQL 2005 I believe the concept is the same. I think the intent of Microsoft had it mind for doing this was to store the date separate from the time values which won't work using the datetime datatype and I have read this in documentation in the past.
The challenge is to convert that data into a humanly legible 12 or 24 hour time format like 11:00 AM or 02:45:39.
Does anyone have any suggestions or clues to assist in resolving this problem??? :(
Thanks.
Hello,
I am new with the reporting services. I am creating a report and I need to display date/time on the report. But the servers stores those date/time in UTC. How can I convert them to the local time in my report.
Thanks for your help.
Guys,
Need ur help on this...how can I change the datetime stamp to GMT in a table, it's a SQL Server 2005 database.
The timestamps are EST,PST,CT...
so have to change the arrival and departure times to GMT time zone
Thanks,
JohnB
I have a field that displays seconds. I need to convert it to hours/min/sec.
For example,
Seconds convertedValue
90 1 min 30 sec
60 1 min 0 sec
95 1 min 35 sec
Is this possible? Or maybe this would be easier:
Seconds convertedValue
90 00:01:30
60 00:01:00
95 00:01:35
Thanks,
Ninel
I have an integer field that has time values.
Here is what I have and what I want.
Example:
0 = "12:00:00 AM"
345 = "12:03:45 AM"
152341 = "3:23:41 PM"
Is this possible.
I have a DB that gets telephone call information from a telephone switch. We use 3rd part software and a db to capture all of this. However for some reason most of the fields are stored as int DateTime is stored as an int. I am able to convert most of the date field from int by doing select convert(datetime,convert(varchar(8), localday)) that works fine the int are in the db as 20061029 for 10-29-2006.
How ever I can't convert the columns starttime and endtime. They are also stored as int but
They don't seem to store a date.
For instance for the date 9/29/2006 the start time of one particular call is
1159533056 and the endtime is 1159533264 All i need to get from this is the time since I already have the date from another column. I can't figure out what this data represents.
How can I convert this to datetime?
Thanks!
Hi,
Hopefully this is an easy one. I need a function to call from my page footer that takes the NOW date/time value and converts it from Eastern Standard Time to Mountain Standard Time. (My server is on EST time and my users are all on MST time) Of course the function needs to handle daylight savings as well.
thanks!
Convert Time Zone:
I have 05/02/2007 10:00AM CST, how can I convert this to EST in SQL Server 2000 function?
Hiiiii All,
I want to convert date n time which is in varchar datatype stored in SQL SERVER 2000, into UK & US current date-time. i.e. in server i have stored indian date n time ,but for UK n US clients i want to show their current date n time , Is this psble in asp.net , please tell me how?
Thnaks in advanced
I need to create a sp for sql 2000 to run with Crystal 7. (No I can't upgrade the crystal). The user wants the calendar prompt to be issues when Crystal with the calendar function. The problem is that if I define the sp var to be passed in as a datetime, crystal wants the hh mm ss also. if I issue it as a varchar(10) and convert it in my select it won't bring up the calendar control. Never tried this but I am guessing you can't do a convert on a var you feed from an sp like below
create procedure checkdate @date convert(varchar(10),@date,101)
as
select * from patient_medication where create_timestamp > @date
Any ideas?
In my database times are stored as integers. Therefore if someone arrives a 1am the value is 60, 2am 120 etc. Is there a way to convert these integer values to 1:00, 2:00. I've tried converting to a decimal so if somene arrives at 1:30 it returns 1.5, but I still only get the integer value...
CONVERT(decimal(10,2), (90/60)) AS iStart
Thanks in advance
Roy
Dear all,
I have 30 tables with the same stracture (01 to 30). One of the fields is duration but has a text data type.
Is there a way to convert the duration field into "Time" date type with format "Long Time" using one query only?
If i have to have one query for each table, can i create a new query or a procedure through a command button that runs all the queries?
Thank you
George
Dear all,
I have 30 tables with the same stracture (01 to 30). One of the fields is duration but has a text data type.
Is there a way to convert the duration field into "Time" date type with format "Long Time" using one query only?
If i have to have one query for each table, can i create a new query or a procedure through a command button that runs all the queries?
Thank you
George
I have a stored procedure, it is accepting a parameter which is in local datetime format. But the front end application is passing UTC date format. How I can convert UTC date time to local date time...
View 2 Replies View RelatedI have an application that spits out data in epoch time? Is there a way to convert epoch time to a standard date/time format?
View 1 Replies View Related Hi All,
Is there any function sql server to convert datetime zone.
For example : EMEA follows GMT time zone.
I want to convert time to other zones which are used by other geos like APAC,IJKK.
Is there any utility on SQL server to do that?
Regards
Abdul
i have a sql statement that i created in code and it is sending a query to the database
when i dim the variable a datetime variable it says that it cant convert it
if i make the variable a varchar it works but it only returns one result when it should be returning about 10
here is the code
Public Function dbDGQSSearch(ByVal BatchID As String, ByVal CreatedBy As Integer, ByVal CreatedFor As Integer, ByVal DateCreatedMod As Integer, ByVal DateCreated As String, ByVal DateCompletedMod As Integer, ByVal DateCompleted As String, ByVal DateStartedMod As Integer, ByVal DateStarted As String, ByVal SearchType As Integer, ByVal Completed As Integer, ByVal PriorityMod As Integer, ByVal Priority As Integer, ByVal RemainingCallsMod As Integer, ByVal RemainingCalls As Integer, ByVal TotalCallsMod As Integer, ByVal TotalCalls As Integer, ByVal Bonus As Integer, ByVal Keyword1 As String, ByVal Keyword2 As String, ByVal Keyword3 As String, ByVal Keyword4 As String, ByVal Keyword5 As String)
Dim strQueSearch As String
strQueSearch = "SELECT tlkup_Rep.RepID, tlkup_Rep.PositionID, tlkup_Rep.RepFName, tlkup_Rep.RepLName, tlkup_Rep.RepPassword, tlkup_Rep.RepUserName, tlkup_Rep.RepFName + ' ' + tlkup_Rep.RepLName AS RepName, t_Que.QueID, t_Que.BatchID, t_Que.AdminID, t_Que.Manager, t_Que.BonusID, t_Que.QueCompleted, t_Que.QueDate, t_Que.QueNotes, t_Que.QuePriority, t_Que.QueQuantity, t_Que.QueStartDate, t_Que.Mail, t_Que.QueDateComplete, t_Que.QueTotal FROM t_Que INNER JOIN tlkup_Rep ON t_Que.Manager = tlkup_Rep.RepID AND t_Que.Manager = tlkup_Rep.RepID WHERE BatchID<>'' and BatchID<>'2' and BatchID<>'3' and BatchID<>'4' "
'Creates statement for selecting the add to batch data where the criteria appear
If BatchID <> "" Then
strQueSearch = strQueSearch + " and t_Que.BatchID= @BatchID "
End If
If CreatedBy > 1 Then
strQueSearch = strQueSearch + " and t_Que.RepID =@RepID "
End If
If CreatedFor > 1 Then
strQueSearch = strQueSearch + " and t_Que.Manager = @Manager "
End If
If DateCreated <> "" Then
If DateCreatedMod = 0 Then
'>
strQueSearch = strQueSearch + " and t_Que.QueDate >@QueDate "
ElseIf DateCreatedMod = 1 Then
'<
strQueSearch = strQueSearch + " and t_Que.QueDate <@QueDate "
ElseIf DateCreatedMod = 2 Then
'=
strQueSearch = strQueSearch + " and t_Que.QueDate =@QueDate "
End If
End If
If DateCompleted <> "" Then
If DateCompletedMod = 0 Then
'>
strQueSearch = strQueSearch + " and t_Que.QueDateComplete >@QueDateComplete "
ElseIf DateCompletedMod = 1 Then
'<
strQueSearch = strQueSearch + " and t_Que.QueDateComplete <@QueDateComplete and t_Que.QueDateComplete >'1/1/1900' "
ElseIf DateCompletedMod = 2 Then
'=
strQueSearch = strQueSearch + " and t_Que.QueDateComplete =@QueDateComplete "
End If
End If
If DateStarted <> "" Then
If DateStartedMod = 0 Then
'>
strQueSearch = strQueSearch + " and t_Que.QueStartDate >@QueStartDate "
ElseIf DateStartedMod = 1 Then
'<
strQueSearch = strQueSearch + " and t_Que.QueStartDate <@QueStartDate "
ElseIf DateStartedMod = 2 Then
'=
strQueSearch = strQueSearch + " and t_Que.QueStartDate =@QueStartDate "
End If
End If
If SearchType = 0 Then
'Both
'strQueSearch = strQueSearch + " and t_Que.Mail=0 and t_Que.Mail=1 "
ElseIf SearchType = 1 Then
'Mail
strQueSearch = strQueSearch + " and t_Que.Mail=1 "
ElseIf SearchType = 2 Then
'Phone
strQueSearch = strQueSearch + " and t_Que.Mail=0 "
End If
If Completed = 0 Then
'Both
'strQueSearch = strQueSearch + " and t_Que.Mail=0 and t_Que.Mail=1 "
ElseIf Completed = 1 Then
'Yes
strQueSearch = strQueSearch + " and t_Que.QueCompleted=1 "
ElseIf Completed = 2 Then
'No
strQueSearch = strQueSearch + " and t_Que.QueCompleted=0 "
End If
If Priority > 0 Then
If PriorityMod = 0 Then
'>
strQueSearch = strQueSearch + " and t_Que.QuePriority >@QuePriority "
ElseIf PriorityMod = 1 Then
'<
strQueSearch = strQueSearch + " and t_Que.QuePriority <@QuePriority "
ElseIf PriorityMod = 2 Then
'=
strQueSearch = strQueSearch + " and t_Que.QuePriority =@QuePriority "
End If
End If
If RemainingCalls > 0 Then
If RemainingCallsMod = 0 Then
'>
strQueSearch = strQueSearch + " and t_Que.QueQuantity >@QueQuantity "
ElseIf RemainingCallsMod = 1 Then
'<
strQueSearch = strQueSearch + " and t_Que.QueQuantity <@QueQuantity "
ElseIf RemainingCallsMod = 2 Then
'=
strQueSearch = strQueSearch + " and t_Que.QueQuantity =@QueQuantity "
End If
End If
If TotalCalls > 0 Then
If TotalCallsMod = 0 Then
'>
strQueSearch = strQueSearch + " and t_Que.QueTotal >@QueTotal "
ElseIf TotalCallsMod = 1 Then
'<
strQueSearch = strQueSearch + " and t_Que.QueTotal <@QueTotal "
ElseIf TotalCallsMod = 2 Then
'=
strQueSearch = strQueSearch + " and t_Que.QueTotal =@QueTotal "
End If
End If
If Bonus > 1 Then
strQueSearch = strQueSearch + " and t_Que.BonusID =@BonusID "
End If
If Keyword1 <> "" Then
strQueSearch = strQueSearch + " and t_Que.QueNotes like '%'+@Keyword1+'%' "
End If
If Keyword2 <> "" Then
strQueSearch = strQueSearch + " and t_Que.QueNotes like '%'+@Keyword2+'%' "
End If
If Keyword3 <> "" Then
strQueSearch = strQueSearch + " and t_Que.QueNotes like '%'+@Keyword3+'%' "
End If
If Keyword4 <> "" Then
strQueSearch = strQueSearch + " and t_Que.QueNotes like '%'+@Keyword4+'%' "
End If
If Keyword5 <> "" Then
strQueSearch = strQueSearch + " and t_Que.QueNotes like '%'+@Keyword5+'%' "
End If
'makes statement into sqlcommand
C.daQueSearch.SelectCommand.CommandText = strQueSearch
'var declaration
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@BatchID", SqlDbType.VarChar, 12))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@Manager", SqlDbType.Int))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@RepID", SqlDbType.Int))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@QueDate", SqlDbType.VarChar, 20)) '<--- This is what,when i change to datetime, says it cant convert
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@QueStartDate", SqlDbType.VarChar, 20))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@QueDateComplete", SqlDbType.VarChar, 20))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@QuePriority", SqlDbType.Int))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@QueQuantity", SqlDbType.BigInt))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@QueTotal", SqlDbType.BigInt))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@BonusID", SqlDbType.SmallInt))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@Keyword1", SqlDbType.VarChar, 50))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@Keyword2", SqlDbType.VarChar, 50))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@Keyword3", SqlDbType.VarChar, 50))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@Keyword4", SqlDbType.VarChar, 50))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@Keyword5", SqlDbType.VarChar, 50))
'data entry
C.daQueSearch.SelectCommand.Parameters("@BatchID").Value = BatchID
C.daQueSearch.SelectCommand.Parameters("@Manager").Value = CreatedBy
C.daQueSearch.SelectCommand.Parameters("@RepID").Value = CreatedFor
C.daQueSearch.SelectCommand.Parameters("@QueDate").Value = DateCreated
C.daQueSearch.SelectCommand.Parameters("@QueStartDate").Value = DateStarted
C.daQueSearch.SelectCommand.Parameters("@QueDateComplete").Value = DateCompleted
C.daQueSearch.SelectCommand.Parameters("@QuePriority").Value = Priority
C.daQueSearch.SelectCommand.Parameters("@QueQuantity").Value = RemainingCalls
C.daQueSearch.SelectCommand.Parameters("@QueTotal").Value = TotalCalls
C.daQueSearch.SelectCommand.Parameters("@BonusID").Value = Bonus
C.daQueSearch.SelectCommand.Parameters("@Keyword1").Value = Keyword1
C.daQueSearch.SelectCommand.Parameters("@Keyword2").Value = Keyword2
C.daQueSearch.SelectCommand.Parameters("@Keyword3").Value = Keyword3
C.daQueSearch.SelectCommand.Parameters("@Keyword4").Value = Keyword4
C.daQueSearch.SelectCommand.Parameters("@Keyword5").Value = Keyword5
Try
C.ndConnection.Open()
C.daQueSearch.SelectCommand.ExecuteNonQuery()
Catch ex As Exception
lblMainError1.Text = err("dbDGQSSearch " + ex.Source, ex.Message, CurUsr)
lblMainError1.Visible = True
Finally
C.ndConnection.Close()
End Try
FillQSDG()' this fills the datagrid
End Function
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>
View 3 Replies View RelatedHello, everyone:
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.
ZYT
Hi,
Can anyone tell me how to convert date and time to string.
I got one field inside my database which contain data like below:
4/16/2004 10:19:01 AM
if i wan to call out a record which have to refer to the data above
Select *
From table_A
where field_date= ???????
what to fill in the ??????
Thanx
Hello,
I have a database field that is just an appointment time but it is stored as a char and it appears to be in military time as well.
For reporting purposes I am creating a query that will use the appointment time and I need it to display in standard time format. I am doing this report in crystal and I tried to use crystal tools but it crystal recognizes it as a string so it won't let me set the time format. Is their a cast or convert or some type of sql function I can use to select the appointment time in standard time format? I have been searching online and just haven't found anything yet.
Thanks in advance!
Sherri
Hi,
I want to convert time 2:8:6 to 02:08:06 in the easiest possible way. How to do it without using Left() or Right()
thanks.
Precisely, here's what I need:When I run getdate(), I get, for example:August 9 2004 5:17 P.M.I want to turn the date portion into:8/9/2004 format and update one column with itI want to turn 5:17 P.M. into:hhmmss and update another column with it.I've been playing around with datepart, with substr, with you name it,and I'm stumped.Any code samples, other help most appreciated.Thanks,Google Jenny*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedAll ,
I need to convert the given time into 24 hour clock .
I have the two tables out of which one contain time in 12 Hr clock and another contain time in 24 Hr clock and i need to make a join on this colum by converting 12 Hr time in to 24 hr clock time.
Please help ..
Regards,
Ashish