Date Query Format Problem

Question

Post reply

Date Query Format Problem

Viewing 15 posts - 16 through 30 (of 46 total)

You must be logged in to reply to this topic. Login to reply

jerome.morris SSCrazy Points: 2829 More actions · Answer 1

jerome.morris

SSCrazy

Points: 2829

September 1, 2012 at 3:23 pm

#1532709

This is slow 🙁

UPDATE ProdDataTB SET StartDate_Time = ''

CREATE TABLE TestTable (Test DATETIME)

insert into TestTable (Test)

select CONVERT(datetime, StartDD+'-'+StartMM+'-'+StartYYYY+' '+StartHH+':'+StartMM+':'+StartSS) from ProdDataTB T;

jerome.morris SSCrazy Points: 2829 More actions · Answer 2

jerome.morris

SSCrazy

Points: 2829

September 1, 2012 at 3:24 pm

#1532710

infact it doenst work

jerome.morris SSCrazy Points: 2829 More actions · Answer 3

UPDATE ProdDataTB SET StartDate_Time = ''

--CREATE TABLE TestTable (Test NVARCHAR(100))

insert into dbo.TestTable (Test)

select StartDD+'-'+StartMM+'-'+StartYYYY+' '+StartHH+':'+StartMM+':'+StartSS from ProdDataTB T;

Select convert(datetime,[Test],103) as [Date] from TestTable

looking better but how do I insert the result into another table

jerome.morris SSCrazy Points: 2829 More actions · Answer 4

Still failing

UPDATE ProdDataTB SET StartDate_Time = ''

--CREATE TABLE TestTable (Test NVARCHAR(100))

insert into dbo.TestTable (Test)

select StartDD+'-'+StartMM+'-'+StartYYYY+' '+StartHH+':'+StartMM+':'+StartSS from ProdDataTB T;

insert into dbo.ProdDataTB (StartDate_Time)

select convert(datetime,[Test],103) as [Date] from TestTable

so slow

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

jerome.morris (9/1/2012)

The table can look like anything really, the rest I can play with. Stagging table sounds good, I just want to remove everything I don't need after but for now I would like to know how to join columns in a way that I can insert into date time column. Sorry if my questions are simple but I am really new to SQL and my project moves faster than my knowledge.

Jay

Your questions really aren't simple because I don't have enough information on what you're trying to do. You know what's happening but we don't.

This is what I think you have, so far...

The table you posted is a staging table and you use it to import data from a file that looks similar to the table. Is that correct or not? Either way, let us know.

Actually, I recommend that you start over and tell us everything so we can help solve this problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

jerome.morris SSCrazy Points: 2829 More actions · Answer 6

jerome.morris

SSCrazy

Points: 2829

September 2, 2012 at 7:58 am

#1532727

Hi your correct, I want to import a cvs to nvarchar columns then export the mentioned above to another table converting to a date time in the process

Jay

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 7

jerome.morris (9/1/2012)

It gets better look at the create for the table

USE [SLADB]

GO

/****** Object: Table [dbo].[ProdDataTB] Script Date: 09/01/2012 12:27:41 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[ProdDataTB](

[MachineName] [nvarchar](10) NULL,

[ModeName] [nvarchar](10) NULL,

[FileName] [nvarchar](10) NULL,

[JobName] [nvarchar](10) NULL,

[UserName] [nvarchar](10) NULL,

[TransactionKey] [int] NULL,

[StartDate_Time] [nvarchar](25) NULL,

[StartDate] [datetime] NULL,

[StartTime] [time](7) NULL,

[StartYYYY] [float] NULL,

[StartMM] [float] NULL,

[StartDD] [float] NULL,

[StartHH] [float] NULL,

[StartNN] [float] NULL,

[StartSS] [float] NULL,

[Spare1] [nvarchar](6) NULL,

[EndDate_Time] [nvarchar](30) NULL,

[EndDate] [datetime] NULL,

[EndTime] [time](7) NULL,

[EndYYYY] [float] NULL,

[EndMM] [float] NULL,

[EndDD] [float] NULL,

[EndHH] [float] NULL,

[EndNN] [float] NULL,

[EndSS] [float] NULL,

[Spare2] [nvarchar](6) NULL,

[RunTime] [int] NULL,

[DelayTime] [int] NULL,

[MachineStopTime] [int] NULL,

[OperatorStopTime] [int] NULL,

[MachineFaultTime] [int] NULL,

[OperatorFaultTime] [int] NULL,

[OldPiecesFed] [int] NULL,

[OldPiecesCompleted] [int] NULL,

[NumMachineStops] [int] NULL,

[NumOperatorStops] [int] NULL,

[NumDelays] [int] NULL,

[Feeder_00_Count] [int] NULL,

[Feeder_01_Count] [int] NULL,

[Feeder_02_Count] [int] NULL,

[Feeder_03_Count] [int] NULL,

[Feeder_04_Count] [int] NULL,

[Feeder_05_Count] [int] NULL,

[Feeder_06_Count] [int] NULL,

[Feeder_07_Count] [int] NULL,

[Feeder_08_Count] [int] NULL,

[Feeder_09_Count] [int] NULL,

[Feeder_10_Count] [int] NULL,

[Feeder_11_Count] [int] NULL,

[Feeder_12_Count] [int] NULL,

[Feeder_13_Count] [int] NULL,

[Feeder_14_Count] [int] NULL,

[Feeder_15_Count] [int] NULL,

[Feeder_16_Count] [int] NULL,

[Feeder_17_Count] [int] NULL,

[Input_Feeder_Count] [int] NULL,

[Input_SubFdr_1_Count] [int] NULL,

[Input_SubFdr_2_Count] [int] NULL,

[Input_SubFdr_3_Count] [int] NULL,

[Input_SubFdr_4_Count] [int] NULL,

[Input_SubFdr_5_Count] [int] NULL,

[Avg_Chassis_Speed] [float] NULL,

[Shift] [int] NULL,

[Total_Pcs_Outsorted] [int] NULL,

[Total_Pcs_Outsorted_Good] [int] NULL,

[Total_Pcs_Outsorted_Maybe] [int] NULL,

[Total_Pcs_Outsorted_Bad] [int] NULL,

[Total_Pcs_Outsorted_Unk] [int] NULL,

[Bin_01] [int] NULL,

[Bin_02] [int] NULL,

[Bin_03] [int] NULL,

[Bin_04] [int] NULL,

[Bin_05] [int] NULL,

[Bin_06] [int] NULL,

[Bin_07] [int] NULL,

[Bin_08] [int] NULL,

[IST_Bin1] [int] NULL,

[IST_Bin2] [int] NULL,

[IST_Bin3] [int] NULL,

[IST_Bin4] [int] NULL,

[IST_RunOut] [int] NULL,

[Mtr1_NoPrint] [int] NULL,

[Mtr2_NoPrint] [int] NULL,

[Mtr3_NoPrint] [int] NULL,

[Mtr4_NoPrint] [int] NULL,

[Edge_Mark1] [int] NULL,

[Edge_Mark2] [int] NULL,

[Edge_Mark3] [int] NULL,

[No_Seal] [int] NULL,

[Empty_Cycles] [int] NULL,

[Filled_Cycles] [int] NULL,

[MidRunTime] [nvarchar](8) NULL,

[SubShiftExt] [nvarchar](2) NULL,

[ShiftDateAdjust] [nvarchar](10) NULL,

[PiecesFed] [int] NULL,

[PiecesCompleted] [int] NULL,

[Spare3] [nvarchar](68) NULL,

[ID] [int] IDENTITY(1,1) NOT NULL,

CONSTRAINT [PK_ProdDataTB] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

Notice the StartYYYY, StartMM, StartDD, StartHH, StartNN, StartSS. these are what is used I assume to make up the strange string StaerDate_Time.

Can I take these values join them together and insert into a datetime column ?

I can then drop these columns from my c# app and have a slick table

Thanks

Jay

As I pointed out on SQL Team - you already have a StartDate and a StartTime column. Why go through all this hassle to tie together the separate date parts when you already have what you need?

To create a datetime column from the StartDate and StartTime columns it is simply: convert(datetime, StartDate) + convert(datetime, StartTime)

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

jerome.morris SSCrazy Points: 2829 More actions · Answer 8

jerome.morris

SSCrazy

Points: 2829

September 2, 2012 at 9:35 am

#1532737

Hi jeff I made them date time, columns trying to sort this mess out

Jay

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 9

jerome.morris (9/2/2012)

Hi jeff I made them date time, columns trying to sort this mess out

Jay

Not sure I understand - it probably would help if you showed us the data you are getting and what you are using to put that into SQL.

It sounds like you are getting data in a text file and putting it into SQL Server. If so, you want to identify the format of the data coming in and either parse it directly into a datetime column or parse it into a date and a time column.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

jerome.morris SSCrazy Points: 2829 More actions · Answer 10

The data I receive is flat fixed width text file, the fastest way I could get this into my DB was to convert to CSV in my c# app and bulk copy to the table. Originally all the columns data types are stringsin ProdData, there is lots of columns and data I dont use but the StartDate_Time format I can work with in my app.

So I want to create a another table and string together the following

StartDD StartMM StartYYYY StartHH StartMM StartSS the copy them to a new table into a datetime column.

Thanks for your support

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 11

CELKO (9/2/2012)

It gets better look at the create for the table

I tried to normalize this crap and choked. Most of the columns can be dropped, but I have no idea how to get rid of the repeated groups. Total nightmare. You need a drink and to update your resuem.

If all you're going to do is be negative and make ad hominem attacks, then just go away. Evenyone including the OP knows there are several problems including normalization and you're comments aren't helping.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 12

jerome.morris (9/2/2012)

The data I receive is flat fixed width text file, the fastest way I could get this into my DB was to convert to CSV in my c# app and bulk copy to the table. Originally all the columns data types are stringsin ProdData, there is lots of columns and data I dont use but the StartDate_Time format I can work with in my app.

So I want to create a another table and string together the following

StartDD StartMM StartYYYY StartHH StartMM StartSS the copy them to a new table into a datetime column.

Thanks for your support

We're getting closer. What we need to see is the record layout for the file, the first 10 lines of the file (including the header if there is one), and what you want for a final table.

Of course, if the file contains any private data, please obfuscate it before attaching the 10 lines of file data.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

jerome.morris SSCrazy Points: 2829 More actions · Answer 13

APS08,C5_LAKE_WE,08075_002_,0807500236,ADAMSAG,26013,Sun Jan 01 07:21:59 2012,01/01/2012,07:21:59,2012,01,01,07,21,59,,Sun Jan 01 07:30:42 2012,01/01/2012,07:30:42,2012,01,01,07,30,42,,196,27,88,212,88,212,498,483,3,0,8,00000487,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00001147,00001147,00000000,00000000,00000000,00000000,13463,,2,0,0,2,0,0,2,0,0,0,,,,,,,,,,,,,,,,,488,245,,,,498,483,,

APS05,C5_LAKE_WE,08066_005_,0806600536,HUGHEPE,28708,Sun Jan 01 07:08:38 2012,01/01/2012,07:08:38,2012,01,01,07,08,38,,Sun Jan 01 07:47:25 2012,01/01/2012,07:47:25,2012,01,01,07,47,25,,1467,45,295,520,295,520,5476,5457,12,2,15,00005467,00000000,00000000,00000000,00000000,00000000,00000109,00004248,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00009894,00000000,00009894,00000000,00000000,00000000,14090,,18,0,0,14,4,11,2,0,0,5,,,,,,,,,,,,,,,,,5467,275,,,,5476,5457,,

This is 2 lines from the csv

the order is the same as the create i posted.

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 14

So, you are getting the data as a distinct date and time. Putting that data into a date column and a time column would be appropriate - and you can then compute the datetime from those two columns as I showed you previously.

I may have responded with that information on SQL Team instead of here - so I will repeat it here:



Declare @StartDate date = getdate()

      , @StartTime time = getdate();

 Select @StartDate, @StartTime, convert(datetime, @StartDate) + convert(datetime, @StartTime);

If your columns are defined as 'StartDate date' and 'StartDate time' - computing the StartDate_Time column is this: convert(datetime, StartDate) + convert(datetime, StartTime).

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

jerome.morris SSCrazy Points: 2829 More actions · Answer 15

Hi Jeff, sorry I am confused a bit, I get a flat text file, then convert to csv and bulk insert all the data into ProdData table which is all NvarChar.

What I want to do is use this as a holding table and move data from it to another table that has the correct datatype.

This is the original create

USE [SLADB]

GO

/****** Object: Table [dbo].[ProdDataTB] Script Date: 09/02/2012 19:28:12 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[ProdDataTB](

[MachineName] [nvarchar](10) NULL,

[ModeName] [nvarchar](10) NULL,

[FileName] [nvarchar](10) NULL,

[JobName] [nvarchar](10) NULL,

[UserName] [nvarchar](10) NULL,

[TransactionKey] [nvarchar](10) NULL,

[StartDate_Time] [nvarchar](25) NULL,

[StartDate] [nvarchar](10) NULL,

[StartTime] [nvarchar](10) NULL,

[StartYYYY] [nvarchar](4) NULL,

[StartMM] [nvarchar](2) NULL,

[StartDD] [nvarchar](2) NULL,

[StartHH] [nvarchar](2) NULL,

[StartNN] [nvarchar](2) NULL,

[StartSS] [nvarchar](2) NULL,

[Spare1] [nvarchar](6) NULL,

[EndDate_Time] [nvarchar](30) NULL,

[EndDate] [nvarchar](10) NULL,

[EndTime] [nvarchar](10) NULL,

[EndYYYY] [nvarchar](4) NULL,

[EndMM] [nvarchar](2) NULL,

[EndDD] [nvarchar](2) NULL,

[EndHH] [nvarchar](2) NULL,

[EndNN] [nvarchar](2) NULL,

[EndSS] [nvarchar](2) NULL,

[Spare2] [nvarchar](6) NULL,

[RunTime] [nvarchar](5) NULL,

[DelayTime] [nvarchar](5) NULL,

[MachineStopTime] [nvarchar](5) NULL,

[OperatorStopTime] [nvarchar](5) NULL,

[MachineFaultTime] [nvarchar](5) NULL,

[OperatorFaultTime] [nvarchar](5) NULL,

[OldPiecesFed] [nvarchar](5) NULL,

[OldPiecesCompleted] [nvarchar](5) NULL,

[NumMachineStops] [nvarchar](5) NULL,

[NumOperatorStops] [nvarchar](5) NULL,

[NumDelays] [nvarchar](5) NULL,

[Feeder_00_Count] [nvarchar](8) NULL,

[Feeder_01_Count] [nvarchar](8) NULL,

[Feeder_02_Count] [nvarchar](8) NULL,

[Feeder_03_Count] [nvarchar](8) NULL,

[Feeder_04_Count] [nvarchar](8) NULL,

[Feeder_05_Count] [nvarchar](8) NULL,

[Feeder_06_Count] [nvarchar](8) NULL,

[Feeder_07_Count] [nvarchar](8) NULL,

[Feeder_08_Count] [nvarchar](8) NULL,

[Feeder_09_Count] [nvarchar](8) NULL,

[Feeder_10_Count] [nvarchar](8) NULL,

[Feeder_11_Count] [nvarchar](8) NULL,

[Feeder_12_Count] [nvarchar](8) NULL,

[Feeder_13_Count] [nvarchar](8) NULL,

[Feeder_14_Count] [nvarchar](8) NULL,

[Feeder_15_Count] [nvarchar](8) NULL,

[Feeder_16_Count] [nvarchar](8) NULL,

[Feeder_17_Count] [nvarchar](8) NULL,

[Input_Feeder_Count] [nvarchar](8) NULL,

[Input_SubFdr_1_Count] [nvarchar](8) NULL,

[Input_SubFdr_2_Count] [nvarchar](8) NULL,

[Input_SubFdr_3_Count] [nvarchar](8) NULL,

[Input_SubFdr_4_Count] [nvarchar](8) NULL,

[Input_SubFdr_5_Count] [nvarchar](8) NULL,

[Avg_Chassis_Speed] [nvarchar](5) NULL,

[Shift] [nvarchar](5) NULL,

[Total_Pcs_Outsorted] [nvarchar](5) NULL,

[Total_Pcs_Outsorted_Good] [nvarchar](5) NULL,

[Total_Pcs_Outsorted_Maybe] [nvarchar](5) NULL,

[Total_Pcs_Outsorted_Bad] [nvarchar](5) NULL,

[Total_Pcs_Outsorted_Unk] [nvarchar](5) NULL,

[Bin_01] [nvarchar](5) NULL,

[Bin_02] [nvarchar](5) NULL,

[Bin_03] [nvarchar](5) NULL,

[Bin_04] [nvarchar](5) NULL,

[Bin_05] [nvarchar](5) NULL,

[Bin_06] [nvarchar](5) NULL,

[Bin_07] [nvarchar](5) NULL,

[Bin_08] [nvarchar](5) NULL,

[IST_Bin1] [nvarchar](5) NULL,

[IST_Bin2] [nvarchar](5) NULL,

[IST_Bin3] [nvarchar](5) NULL,

[IST_Bin4] [nvarchar](5) NULL,

[IST_RunOut] [nvarchar](5) NULL,

[Mtr1_NoPrint] [nvarchar](5) NULL,

[Mtr2_NoPrint] [nvarchar](5) NULL,

[Mtr3_NoPrint] [nvarchar](5) NULL,

[Mtr4_NoPrint] [nvarchar](5) NULL,

[Edge_Mark1] [nvarchar](5) NULL,

[Edge_Mark2] [nvarchar](5) NULL,

[Edge_Mark3] [nvarchar](5) NULL,

[No_Seal] [nvarchar](5) NULL,

[Empty_Cycles] [nvarchar](6) NULL,

[Filled_Cycles] [nvarchar](6) NULL,

[MidRunTime] [nvarchar](8) NULL,

[SubShiftExt] [nvarchar](2) NULL,

[ShiftDateAdjust] [nvarchar](10) NULL,

[PiecesFed] [nvarchar](6) NOT NULL,

[PiecesCompleted] [nvarchar](6) NULL,

[Spare3] [nvarchar](68) NULL,

[ID] [int] IDENTITY(1,1) NOT NULL,

CONSTRAINT [PK_ProdDataTB] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

Could you provide a sample of how to copy startdate_time in correct format and copy to table b column b for example

thanks again