Inserting today's date

Transact-SQL
1

Hello All,

I need some help in this logic ..

I have a code snippet in SQL which results in data from a particular production data based on a hard coded data. I need to get the current date when I run the SQL ..

Select ProductionOrder, ItemNo, COUNT(ItemNo) As Item
From RawData
Where Left(DTStamp,10) = '11/3/2015'
Group by ItemNo, ProductionOrder
Order by ProductionOrder

I need to replace the 11/3/2015 with a dynamic current date ... like -

SELECT Convert(varchar(10), GETDATE(),101)

How do I solve this ?

Thanks,

Mohit.

2 
Where Left(DTStamp,10) = Convert(varchar(10), GETDATE(),101)

If you have any control over it, storing the DTStamp as a DATETIME or similar data type rather than as a character data type is much more preferable.

3

We can get rid of the function on the column, which should help, since that column should be indexed:

Where DTStamp Like Convert(varchar(10), GETDATE(), 101) + '%'

But note that format 101 will have a leading zero in the day, i.e., 11/03/2015 not 11/3/2015.

If you need to get rid of the leading zero to match the data as it actually appears in the table, do this:

Where DTStamp Like Replace(Convert(varchar(10), GETDATE(), 101), '/0', '/') + '%'

4

Thnaks JamesK.

I had tried the code before posting in the forum. It works as long as there are 10 character in the fields. But I have an issue where my date field contains 11\3\15 also. Since I have no control over the table structure and other programs inserting the data into the table, I am unable to modify or edit the data to reflect 10 characters.

5

Thanks Scott ! Your code works for the leading Zero in the day scenario, but the other code does not. I do have data in the date field containing non '0' items eg 11/3/2015.

Where DTStamp Like Replace(Convert(varchar(10), GETDATE(), 101), '/0', '/') + '%'
Did not work. Any advice ?

Thanks,

Mohit.

6 
Where DTStamp LIKE 
    STUFF(Replace('/'+Convert(varchar(10), GETDATE(), 101), '/0', '/'),1,1,'') + '%'
7

D'OH, sorry, quite right, didn't mean to strip the '/', was in too big a hurry there :slight_smile: .

8

What if I have a mix of data - '11/03/2015' & '11/6/2105'. This table is used in production, hence cannot modify or edit.
The applications writing to the Database are produced by several vendors and some stay with single digit days or some with double digit days ?

Advice ?

Thanks,

Mohit.

9

Cast the string to date and then compare as shown below.

WHERE CAST(DTStamp AS DATE) = CAST(GETDATE() AS DATE)

This is assuming that everyone enters the date in the M/D/Y format. If someone decided that they wanted to enter a date as 25/11/2015, and some others entered it as 11/25/2015, there is no deterministic way in which you can parse that data.

The problem with the above, as @ScottPletcher pointed out, is that the performance is likely to be poor.

10

Thanks James & Scott !

11

I'd use an OR condition. You can still get an index search that way.

Where (DTStamp LIKE Convert(varchar(10), GETDATE(), 101) + '%' OR
DTStamp LIKE STUFF(Replace('/'+Convert(varchar(10), GETDATE(), 101), '/0', '/'),1,1,'') + '%')

12

Thanks Scott !! That worked perfectly !