5

Problems with dates in SQL

 2 years ago
source link: https://www.codesd.com/item/problems-with-dates-in-sql.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

Problems with dates in SQL

advertisements

I'm writing a report that needs to collect data each day, between 0900hs and 1700hs. I thought it would be fine as follows:

cast(convert(char(8),t.trxtime,112)as time)
 between  CONVERT(VARCHAR(5),getdate(),108) >= '09:00'
  and CONVERT(VARCHAR(5),getdate(),108) < '17:00'

....BUT no cigar.

Thank you!!!

Hmmm, you could just use datepart():

where datepart(hour, t.trxtime) between 9 and 16 and
      cast(t.trxtime as date) = cast(getdate() as date)

I'm not sure if the date comparison is actually necessary.

Related Articles

Problem with date format using SSIS for Excel in SQL Server

I am trying to import a column of dates from a spreadsheet in Excel 2003 into SQL Server 2005 using SSIS. I am in the UK so want dates formatted as dd/MM/yyyy. Unfortunately, the column in the spreadsheet contains a mixture of dates stored as strings

sql server manager - problems with date

I habe a problem with the new lead olap function in sql server 2012. CREATE TABLE Atext (id int, bez varchar(10), von date); GO INSERT INTO Atext VALUES (1, 't1', '2001-01-01'), (1, 't2', '2012-01-01'), (2, 'a1', '2020-01-01'), (2,'a1' , '2030-01-01'

Problems with converting oracle.sql.TIMESTAMPTZ to string value

I try get some data from oracle db, put this data in json and use it in other place, but i have problem with converting timestamptz. Oracle give me timestamp with timezone in string format like "23.10.14 18:34:16,000000 ASIA/NOVOSIBIRSK". Here s

Problem with Trigger in SQL Server 2005

I am having a problem with a trigger in SQL Server 2005, I have created the trigger and I have tested it inserting rows manually and it works fine that way, however I call a Stored Procedure from a c# web application and the triggers does not get fir

Problem with date comparison in Oracle

I am doing a date comparison in oracle. When I execute the code below select tr.x, tbi.y, to_char(tr.UPDATED_AT,'dd-mon-yyyy') from tbi, tb, tr where tbi.id=tb.id and tb.id=tr.id and tr.updated_at = to_date('23/02/2016', 'dd/MM/yyyy') No row is selec

Problem with updating the SQL Server 2005 table variable

I have been reading about the differences between Table Variables and Temp Tables and stumbled upon the following issue with the Table Variable. I did not see this issue mentioned in the articles I pursued. I pass in a series of PKs via a XML data ty

Request multiple min / max columns with date in SQL

I have a SQL table which look like : Date temp dewpoint humidity 10/12/15 5.1 2.4 57 11/12/15 4.1 5.8 69 12/12/15 20.6 3.6 56 13/12/15 13.0 2.5 21 14/12/15 5.6 13.6 15 15/12/15 5.1 2.4 56 I would like to know it it's possible to print this out with o

NHibernate proxy causes problems with data binding

I have a gridview that is bound to the result from an nhibernate query. If the first item in the list is edited the following exception is thrown: System.Reflection.TargetException: Object does not match target type It appears that the problem is cau

Dynamic Diagram of Sankey with Data from SQL Server 2012

Please suggest me ideas on the below requirement. My requirement is to create a dynamic Sankey diagram which gets generated from the SQL Server Data. For example, when user clicks a dropdown, the dropdown should be passed as input to the SQL Server D

Problem with date formats in JavaScript with different browsers

I am working with dates in an RSS feed, but am finding differing results when using the code below in IE, Chrome and Firefox: new Date('2001-01-01T12:00:00Z') Firefox is happy with that, but Chrome and IE return Invalid Date. I thought I'd try replac

Problem with the reserved SQL keyword when you try to generate Spring Roo data on demand

I am trying to generate a Data On Demand class for one of my entities whose class name is Member. However it seems that Member is a reserved SQL keyword. So when I run the following command in the Roo shell: dod --entity ~.domain.Member Roo complains

Problem with data type Money in SQL SERVER vs string

I have a spreadsheet that gets all values loaded into SQL Server. One of the fields in the spreadsheet happens to be money. Now in order for everything to be displayed correcctly - i added a field in my tbl with Money as DataType. When i read the val

Java problem with date

I have a problem in java with sql date. I have the value of year-y, month-m,day-d I create new Date(y,m,d) but it says that this constructor is deprecated and it returns wrong date, how can I correctly set date, having y,m,d ?According to the API, As

Problems with date and writing when exporting data to a text file in JAVA

I'm running this piece of code to get the date and store it in a log file String myDate =""; Date date = new Date(); DateFormat df = new SimpleDateFormat("yyyy-mm-dd HH:mm:ss"); df.setTimeZone(TimeZone.getTimeZone("America/Chicago

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK