How can I create a field in a view that takes a date field and
converts it to a month?
For instance, if I have a field called "CreatedOn" with a date of
'2/22/2007', how can I create another field called "Month" that will
say "February"? Is there a built-in SQL function that does this (like
using CONVERT) or do I need to write my own?
Thanks!
Lisa
I have a table with Month , Year as varchar. I realized it was a big mistake. Since its getting too complicated to query this way.
Year Month Productname
2013 11 ACB
2013 11 CDE
I would now like to add another column called date and store these Year Month as a date to my existing table
Year Month ProductName Date
2013 11 ACB 2013-11-01
2013 11 CDE 2013-11-01
Is there a way I can do it for all the columns of the existing table ??
My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see
Select
Jan 1- 23rd
feb 1-23rd
march 1-23rd
april 1-23rd
,value
from
table
I am trying to convert a decimal date value to a date value that I can use in formulas but am having a lot of trouble.My date value currently shows in decimal format YYYYMMDD. I want to convert this to a date so I can then find the number of days between two dates.I have tried convert (datetime, convert(varchar(8),left(qhstdt,8))) with qhstdt as my decimal date field but I receive the error message below:
Error: SQL0204 - CONVERT in *LIBL type *N not found.
I have also tried converting it using (year(QHSTDT)*10000+100*month(QHSTDT)+ day(QHSTDT))) but when I convert the dates using this formula, I can get an incorrect number of days when I try to subtract one from the other.What formula can I use to convert my YYYYMMDD field to a format that will allow me to compare number of days between two dates?
I have a 6 char field which has to be converted to a datetime. I thought I had it solved when I did this
convert(datetime,(left(dob,2)+'-'+substring(dob,3,2)+'-'+right(dob,2)))
Problem is with a date value of 081649
I get 08/16/2049 instead of 1949, where did I goof
I copying data from our Informix 7.2 database into SQL Server 2K using DTS but hitting errors during the process. There appears to be date data within Informix that will not convert properly when moving into SQL. Since the error is appearing at the 1.5million (approx.) record. I figured on changing from datetime to nvarchar. Works like a charm! :-)
My new problem is converting it back to datetime so I can query against the date without having to create scripts to parse the field.
The data in SQL currently looks like this -> 2000-11-29 (nvarchar(50))
I would like to have it -> 11/29/00 (datetime)
Any help is greatly appreciated!
JT
The goodness we share is rewarded twice!
In my database . Some to the dates has been stored wrongly .
for example
03/04/2008 to be stored in database . But in my database it was stored as 04/03/2008 . like this i have more that 100 records . How can i change this to correct format using query . Guide me urgent .
Hello,
I'm having a problem with converting calender months to fiscal months.
For April (04) calender month, I have 11 as my fiscal month like below.
[FISCAL CALENDAR].[DAY].&[11]&[2008-04-15T00:00:00]
Here is a parameter 'param' which is a 'datetime' datatype, so i'm basically passing a calender date , so i wrote the query as following:
="[FISCAL CALENDAR].[DAY].&[" + (Month(Parameters!param.Value)+7).ToString + "].&[" + CDate(Parameters!param.Value).ToString("s") + "]"
But it is wrong as I get results like the following:
For April (04), I get 11, which is correct.
[FISCAL CALENDAR].[DAY].&[11].&[2008-04-15T00:00:00]
But for June (06), I get 13 when I'm supposed to get 1.
[FISCAL CALENDAR].[DAY].&[13].&[2008-06-15T00:00:00]
How do I change my query? Please help. I'm struggling with this since yesterday.
DECLARE @datetimeoffset datetimeoffset(3)
DECLARE @datetime datetime
SELECT @datetimeoffset = '2012-11-08T17:22:13.575+00:00'
SELECT @datetime = @datetimeoffset
SELECT @datetimeoffset AS '@datetimeoffset ', @datetime AS 'datetime'
__________________________________________________ ___________
Result of above SQL is
@datetimeoffset datetime
2012-11-08 17:22:13.575 +00:002012-11-08 17:22:13.577
__________________________________________________ ____________
The result should be '2012-11-08 17:22:13.575', why the milliseconds value is incorrect
Aim – Convert the following field ”[INSTALLATION_DATE]” date format from “20090709” Into this “2009-07-09” ,
Also create a new column called “BegMonth” which selects first day of the given month of the converted date column
The table is ;
SELECT
[FDMSAccountNo],
[INSTALLATION_DATE]
FROM [FDMS].[dbo].[stg_LMPAB501]
Results
FDMSAccountNoINSTALLATION_DATE
87800000088420030521
Required Results
FDMSAccountNoINSTALLATION_DATEBegMonth
8780000008842003-05-212003-05-01
how to write a query to get current date or end of month date if we pass year and month as input
Eg: if today date is 2015-09-29
if we pass year =2015 and month=09 then we have to get 2015-09-29
if we pass year =2015 and month=08 then we have to get 2015-08-31(for previous months we have to get EOMonth date & for current month we have to get current date).
We made a poor decision a long time ago when designing our databasestructure. We used bigint data types as the identity keys for many ofour base tables. For many reasons I would like to change these fieldsto int at the largest. The largest data in these fields is around200,000. I know that int can easily store this.What should I be worried about when changing these fields from bigintto int? If anything. Your help is appreciated. I did severalsearches without much luck.
View 5 Replies View RelatedI have a table with an integer field (contains test values like 2, 7,8,9,12,..) that I want to convert to an Identity field. How can this be done in t-sql?
TIA,
Barkingdog
Hello All,
i have three textboxes in a page and i want fill those textboxes with the date, month,year respectively.....
i have a datecreated column in discount table in a mm/dd/yy format ...how to extract the date, month, year from this format and put the value in textboxes..?
Any help..
Thanks..
Anne
hi need help to solved date calculation for next month
i explain
i have table employee and the employee insert into table the holidays
the date start >>>> to date end
now i need to create a view only for next moth , in this view i need to see only the relative dates for the next month if the dates is not for the next month i don't need to see it
like this example 09/07/2008 > 09/08/2008 (is not for next month)
like this example 10/09/2008 > 12/09/2008 (is not for next month)
555
EEE
09/07/2008
09/08/2008
4
666
fff
10/09/2008
12/09/2008
1
in this example i need to see only the relative dates for next month only in the view
333
cccc
01/05/2008
15/05/2008
4
4
333
cccc
01/05/2008
31/05/2008
1
30
tb_all_holiday before
id
fname
Start_Date
EndDate
val_holiday
111
aaaa
15/03/2008
10/05/2008
1
222
bbbb
02/05/2008
31/05/2008
3
333
cccc
03/04/2008
15/05/2008
4
333
cccc
29/04/2008
07/07/2008
1
444
dddd
01/05/2008
02/05/2008
1
444
dddd
09/05/2008
19/08/2008
1
555
EEE
09/07/2008
09/08/2008
4
666
fff
10/09/2008
12/09/2008
1
VIEW_all_holiday after -next month only
id
fname
Start_Date
EndDate
val_holiday
sum_day_next_month
111
aaaa
01/05/2008
10/05/2008
1
4
222
bbbb
02/05/2008
31/05/2008
3
29
333
cccc
01/05/2008
15/05/2008
4
4
333
cccc
01/05/2008
31/05/2008
1
30
444
dddd
01/05/2008
02/05/2008
1
1
444
dddd
09/05/2008
31/05/2008
1
22
all the time i need to see only the relative dates for the next month only
tnx
I am trying to SUM a column of ActivityDebit with current Calendar_Month to a Column of Trial_Balance_Debit from Last Calendar_Month. I am providing Temp Table code as well as fake data.
=====
IF OBJECT_ID('TempDB..#MyTrialBalance','U') IS NOT NULL
DROP TABLE #MyTrialBalance
CREATE TABLE #MyTrialBalance (
[Trial_Balance_ID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL,
[FISCALYEAR] [smallint] NULL,
[Code] ....
Here is my Query I am trying but not working. I cant figure out how to doo the dateadd for correct column.
SELECT A.Trial_Balance_ID,A.ACTIVITYDEBIT --SUM(A.ACTIVITYDEBIT + B.Last_Trail_Balance_Debit) AS New_TB
FROM
(SELECT [Trial_Balance_ID], [Calendar_Month],[ACTIVITYDEBIT]
FROM Mytrialbalance
WHERE actindx='48397' AND ACTIVITYDEBIT='820439.78000'
)A INNER JOIN
(SELECT [Trial_Balance_ID],DATEADD(MM, -1,Calendar_Month)AS Last_Month
FROM Mytrialbalance) B ON B.Trial_Balance_ID=A.Trial_Balance_ID
I have the following
Column Name : [Converted Date]
Data Type : varchar(50)
When I try and do month around the [Converted Date] I get the following error message
“Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.”
My Query is
SELECT
month([Created Date])
FROM [FDMS_PartnerReporting].[Staging].[Salesforce_MarketingReporting]
SELECT * ,[Due]
FROM [Events]
Where Due >= getdate() +90
This returns the error: Conversion failed when converting date and/or time from character string
Why would this be? How to cast or convert this so that it will work?
Hi,
I have julian date in the format of 2003182 and I need to convert into our regular calendar date.
Anybody have any SQL statement for this.
Thanks,
Ravi
The dB dates are store in db as 20140303 when I bring this to my application to pick a date, the apps shows the date as 20140303 how can I change this to show in the format 03/03/2014 in my application?
View 3 Replies View RelatedGood morning...
I begin with SQL, I would like to add a field that will be date like 21/01/2000.
Actually i find just "datetime" format but give me the format 21/01/2000 01:01:20.
How to do for having date and time in two different field.
Sorry for my english....
Cordially
A newbie
I have a SmallDateTime data type field. The SmallDataTime contains day/month/year hour:minuteeconds AM/PM. What I wanted to do is create a store procedure that will only take the month of the field and compare that to my variable. So for example, I wanted to something like the following:
@month varchar(50) = "3"
SELECT * FROM myTable WHERE monthField = @month
Any help is much appreciated.
I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.
I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits.
The mfg_start_date is delcared as a string variable
mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)))
option 1
I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.
With refresh_shipping_sched
.ActiveConnection = CurrentProject.Connection
.CommandText = "spRefresh_shipping_sched"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@option", adInteger, adParamInput, 4, update_option)
.Parameters.Append .CreateParameter("@mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "")
.Parameters.Append .CreateParameter("@mfg_start_date", adChar, adParamInput, 10, "")
Set rs_refresh_shipping_sched = .Execute
End
Please help
I embedded a SQL query in excel that gets some datetime fields like "TASK_FINISH_DATE" .
How can I convert a datetime field to a date field in SQL in a way that excel will recognize it as a date type and not a text type?
I tried:
CONVERT(varchar(8),TASK_FINISH_DATE ,3)
CONVERT(Date,TASK_FINISH_DATE ,3)
CAST(TASK_FINISH_DATE as date)
**all of the above returned text objectes in excel and not date objects.
I am trying to drag data from Informix to Sql Server. When I kick off the package
using an OLE DB Source and a SQL Server Destination, I get DT_DBDATE to DT_DBTIMESTAMP
errors on two fields from Informix which are date data ....no timestamp part
I tried a couple of things:
Created a view of the Informix table where I cast the date fields as datetime year to fraction(5), which failed.
Altered the view to convert the date fields to char(10) with the hopes that SQL Server would implicitly cast them
as datetime but it failed.
What options do I have that will work?
Hi all,I have a SP that is passed a date, and then need to do a test of some data returned from the DB by getting the passed Date, finding out its month, and then using the BETWEEN clause to get all rows from teh DB where their date falls between the 1st and the last (28th, 30th, 31st) of the month contained in the Date that is passed to the SP. I currently have the following SQL in my WHERE clause to get the first day of the month, but it seems long-winded. Is there a smaller, smarter way of getting it...DATEADD(MONTH, +1, DATEADD(MONTH, -1, @RequiredMonth))ThanksTryst
View 2 Replies View RelatedIs there any function in sql to find last date of the given month?
View 5 Replies View RelatedI'm trying to write a query that will tell me the day of the week for the 20th of any given month.
View 6 Replies View RelatedI have a question about creating view for these coloum and have a lot trouble, before that I describe my tables :
Table A :
Code as nvarchar
Ammount1 as money
Date as datetime -> transaction date
primary key are Code and Date1
Table B :
Code as nvarchar
Ammount2 as money
Date as datetime -> transaction date
primary key are Code and Date2
I like joining this table and create table/view that result :
Code | Amount1 Today | Amount2 Today | Amount1 Last Year | Amount1 Month to date (Sum from first day month till today) | Amount2 month to date | Amount1 Last Year month to date
The parameter only for @Date.
First I have problem to get queries specially for first date in the month, I used CAST but it doesn't work, then I used CONVERT and CAST still doesn't work.
Anybody help me ?
Thx a lot.
ad1k4r4
Hello all,
Is there a way to set a variable that includes the first day of the last month.
For example, we are june 2nd. I want a function that returns - for every day in june - 2008/05/01.
Thanks,
Raph.
Hi guys,
How to get the first day of the month.
Say the given Cut Off Date is 11/30/2005. I need to get the StartDate as 11/01/2005.
Thank you.
Hi All,
Can anyone tell me the function name by which I can get the month Name form a date value
When I am using the command print month('2006-09-26 16:30:18.793')in SQL Analyzer window it is giving 6as output but i need the name i.e. JUNE
I need to sort some data by month or extract data for a particular month. My date format is 5/7/2007 11:38:54 AM. What is the easiest way to achieve this?
Melvin Felicien
IT Manager
DCG Properties Limited