|
Hi Chris,
I need my dates as string because I'm working with a Data warehouse DB.
, CAST(Replace(convert(date, [START_DATE], 101), '-', '') as numeric(8,0)) AS [START_DATE]
, CAST(dbo.LPAD(dbo.CONVERT_TIME_TO_NUMERIC(SUBSTRING(dbo.arKD.START_TIME,1,8)), 6, 0)AS nvarchar(6)) AS START_TIME
, CAST(Replace(convert(date, END_DATE, 101), '-', '') as numeric(8,0)) AS END_DATE
, CAST(dbo.LPAD(dbo.CONVERT_TIME_TO_NUMERIC(SUBSTRING(dbo.arKD.END_TIME,1,8)), 6, 0)AS nvarchar(6)) AS END_TIME
,Datediff(SECOND,
convert(datetime, [START_DATE] + ' ' + [START_TIME]),
convert(datetime, [END_DATE] + ' ' + [END_TIME])) AS M_Time_Worked
This is actual code where I Cast all dates to nvarchar.
So how can I change this code in a way that I can set the Start_Date (when empty) to the value of End_Date and Start_Time to the value of End_Time when empty?
I'm not an experienced developer, I'm still learning and hope you can learn me something new.
Kind regards,
Ambertje
|
|
|
|
|
,Datediff(SECOND,
convert(datetime, [START_DATE] + ' ' + [START_TIME]),
convert(datetime, CASE WHEN RTRIM([END_DATE]) = '' THEN [START_DATE] ELSE [END_DATE] END + ' ' + CASE WHEN RTRIM([END_TIME]) = '' THEN [START_TIME] ELSE [END_TIME] END)) AS M_Time_Worked
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
Still an error:
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.
|
|
|
|
|
Ambertje wrote: I need my dates as string because I'm working with a Data warehouse DB.
Why would the date format be a problem in a Data Warehouse?
|
|
|
|
|
Are you looking for ISNULL(START_DATE , END_DATE) ?
|
|
|
|
|
Good morning to you all,
It's Start_Date and Start_Time that can be empty.
So I think ISNULL(Start_Date, Start_Time) is what I need.
How can I implement this in my code?
,Datediff(SECOND,
convert(datetime, [START_DATE] + ' ' + [START_TIME]),
convert(datetime, [END_DATE] + ' ' + END_TIME])) AS M_Time_Worked
Kind regards,
Ambertje
|
|
|
|
|
Ambertje wrote: So I think ISNULL(Start_Date, Start_Time) is what I need.
That is not how ISNULL works. You would need something like
ISNULL(Start_Date, EndDate) + ' ' + ISNULL(Start_Time, Endtime)
|
|
|
|
|
You are correct, but still it wont work like that.
Something was missing and after analasys I saw that the Start_Date field wasn't NULL but just empty.
So this is what I changed to make it work:
,Datediff(SECOND,
convert(datetime, ISNULL( NULLIF([START_DATE], '' ) , END_DATE) + ' ' + ISNULL( NULLIF([START_TIME], '' ) , END_TIME)),
convert(datetime, [END_DATE] + ' ' + [END_TIME])) AS M_Time_Worked
A big thank's to all for helping me and the responses where I learned from.
Kind regards,
Ambertje
|
|
|
|
|
You are correct but when I run this statement I get no values.
So I went on to try some more ways to make it work and this statement works:
, CASE WHEN [START_DATE] IS NULL OR [START_DATE] =
CAST(Replace(convert(date, END_DATE, 101),
CAST(Replace(convert(date, [START_DATE], 101),
But I can't integrate it in my DateDiff statement, can you help me please?
Ambertje
|
|
|
|
|
I have a little bit of SQL code and I am having trouble bringing it over to MSSQL. I don't even know where to being I know that the SELECT, FROM , GROUP BY and HAVING will be in the MSSSQL query.
However I know for a fact the 4th line will change into @param1 and @param2 . If anyone would like to add some input into this would be nice.
Access Query
Select COUNT(') AS Result, Username AS UsernameInput, [Password]
FROM access
GROUP BY Username, [Password]
Having (COUNT(') = 1) AND (Username = ?) AND ([Password] = ?)
modified 24-Jan-15 16:04pm.
|
|
|
|
|
Change the single quote ' to an asterisk * in the count functions. eg count(*)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Asalam Alaikum Friends;
need a help of ERD Fee Record keeper of whole year for a single student plz plz plz help
|
|
|
|
|
Please do not crosspost; use one forum only.
|
|
|
|
|
I'm still trying to work out what he is asking for! ERD, I think is Enterprise Resource something, I have no idea what an annual record keeper is in that context.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Google has: Entity Relationship Diagram. As to the rest, I suspect OP has not really thought the question out too well.
|
|
|
|
|
i need the name of the tables of fee management system database which store like below shap
Month | jan | feb | Mar|
------------------------
curren fee| 100 | 100 |
--------------------------
dues | 200 | 100 |
----------------------------
total | 300 | 200 |
--------------------------
paid | 200 | 50 |
-------------------------
balance | 100 | 150 |
---------------------------------------------
Description
---------------------------------------------------
admissionFee| tutionFee| examFee| otherFee | Total |
----------------------------------------------------
200 | 100 | 30 | 0 | 330 |
-----------------------------------------------------
sorry weak in presentation but hope this time you will understand
|
|
|
|
|
i need the name of the tables of fee management system database which store like below shap
Month | jan | feb | Mar|
------------------------
curren fee| 100 | 100 |
--------------------------
dues | 200 | 100 |
----------------------------
total | 300 | 200 |
--------------------------
paid | 200 | 50 |
-------------------------
balance | 100 | 150 |
---------------------------------------------
Description
---------------------------------------------------
admissionFee| tutionFee| examFee| otherFee | Total |
----------------------------------------------------
200 | 100 | 30 | 0 | 330 |
-----------------------------------------------------
sorry weak in presentation but hope this time you will understand
|
|
|
|
|
Since we have no idea what your "fee management system database" looks like, we cannot answer the question.
|
|
|
|
|
can i send u a picture sketch sir for better understanding
|
|
|
|
|
|
dost main ne jo sketch beja ta dar asal ye fee receipt ka style hai jo student ko diya jata hai school main to ese tarah main be try karta ho to moje tables our is ke attribute chaheya
|
|
|
|
|
Please post only in English in these forums.
|
|
|
|
|
Hi All
I am having sqlserver 2008R2 database, and in the table, there is a column "Text" with some string value.
Some of the strings are good (have the characters entered from keyboard" where some have values like "degree" symbol.
example:
Row 1 has string "Today temperature is 45Degrees C" where as Row 2 has string "Today temperature is 40°C
Row 2 has a symbol which is not entered through the keyboard. I want to filter such kind of symbols while querying.
Is there a way to query that way by filtering? If so, please provide me such query.
Thanks in advance,
Usha
Naina
|
|
|
|
|
That'd be rather slow; but you could loop through all chars, see if it falls within an expected range (say, ASCII upto 127), put that in a function, and call that from the query.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
There are multiple ways to do it. i would suggest you should review your data to identify how many other invalid characters you have after this just use the simple REPLACE() function to remove those. Furthermore, if you are planing to create a function for this then you can look into this Link.
hope it helps.
|
|
|
|